Understanding Filters in Power BI

Filters in Power BI are the tools that help us narrow down our data which ensures that only relevant information is displayed in our reports and visuals. Filters allow us to focus on specific subsets of data based on predefined conditions which makes it easier to analyze large datasets efficiently. Whether we're filtering data at the visual, page or report level it provide a systematic way to refine our analysis.

Types of Power BI Filters

Power BI provides four main types of filters that can be applied at different levels which helps us to manage our data more effectively:

1. Visual-level Filters: These filters apply to individual visuals within a report like tables, charts or cards. They allow us to control how data is presented within a specific visual, giving us flexibility to focus on the data that's most important for that visual. We can use sub-filter modes like Basic filters, Advanced filters and Top-N filters to adjust the data within that visual.
2. Page-level Filters: Page-level filters apply to all visuals on a specific page of the report. When a page-level filter is applied, it automatically updates every visual on the page based on the filter's criteria. This is useful for ensuring that the entire page reflects the same filtered data helps in providing a cohesive view of the report.
3. Drill-through Filters: Drill-through filters allow us to explore data in more depth by navigating between different pages. We can set up drill-through filters to link pages and filter the data dynamically so we can analyze detailed subsets of data without overwhelming the user with too much information on a single page.
4. Report-level Filters: These filters apply to all visuals across all pages in the report. Report-level filters are useful when we want to maintain consistency across all pages helps in ensuring that the same filtering criteria are applied throughout the report.

Add a Filter to a Report in Power BI

There are three main filter modes in Power BI that help us control which data is displayed:

1. Basic Filtering

Basic filtering is the simplest and most intuitive filter mode. It provides a scrollable and searchable list of values for a specific field in our data. We can quickly search for a value and select it or use checkboxes to select multiple values. This mode is ideal for situations where we simply want to focus on one or more data points without needing to apply complex conditions. For example, we can filter by specific countries or product categories with ease.

2. Advance Filtering

Advanced filtering gives us greater control over how data is filtered by allowing us to define more specific criteria. Instead of just selecting from a list, we can apply rules such as greater than, less than, between a range of values or even exclude certain values. This mode is useful when we need to filter data based on numerical ranges or specific conditions. For example, we might want to exclude certain countries from the report or filter products with sales above a specific threshold.

3. Top-N Filtering

Top-N filtering is used when we want to focus on the highest or lowest N values in a dataset. This filter type allows us to quickly identify top performers or outliers. We can specify how many top or bottom items we want to display such as the top 5 countries by sales or the bottom 3 products by revenue. Top-N filters are useful in identifying trends or analyzing the extremes within our data. For example, we might filter for the top 10 products in terms of sales or the lowest 5 regions in terms of performance.

How to add Power BI Filter to a Visual?

We can add Power BI filters to a Visual using the following steps:

Step 1: Select the Visual

First, we select the visual we want to apply the filter to. This could be any chart, table or card. Once we select the visual the "Filters on this visual" option will appear in the Filters panel on the right side of the screen. For example, after selecting a pie chart, we will see the "Filters on this visual" section appear showing that we can now apply filters specific to that visual.

Step 2: Drag and Drop Fields

Next, we drag and drop the desired fields from the Fields pane into the appropriate filter areas in the Filters panel. For example, we can drag the Country, Product and Quantity Sold fields from the data panel and drop them into the relevant filter sections. This action will populate the visual with data based on these fields.

Step 3: Apply the Filter

After adding the fields, the visual will automatically update to reflect the applied filter. In the case of a pie chart, as seen in the screenshot, the visual will display the filtered data accordingly. Filters are typically applied automatically when we add fields but we can fine-tune the filter options to control which data is displayed more precisely.

How to add a Power BI Filter to a Page?

We can add a Power BI filter to a Page using the following steps:

Step 1: Create Multiple Visuals

First, create at least two visuals on the page like a pie chart and a table. This helps us see the effect of page-level filters on multiple visuals.

Step 2: Use Visual-Level Filters

Initially, we apply visual-level filters to populate each visual individually. As we can see in the screenshot we populate the visuals (pie chart and table) by selecting the relevant fields like Product, Sales, Quantity, etc from the data panel. This helps us set up the initial view of the dashboard as shown below:

Step 3: Apply Page-Level Filters

To apply a page-level filter, we drag and drop the desired data fields into the "Filters on this page" section. For example, we drop the Product field into this section. This will allow us to filter the entire page based on the selected field.

Step 4: Select Filter Values

Next, we select the specific values we want to filter across all visuals on the page. For example, we select CPU, Hard Disk and Pad from the Product field and these values will update across all the visuals on the page.

As us can see in the below screenshot, the pie chart and table two visuals get updated by in its product field.

Note: To understand and use page-level filters us must have to create two visuals on the current page.

How to add Power BI Filter to a Report?

We can add Power BI Filters to a report with the help of the following steps:

Step 1: Create a Visual

To use the report level filter create a visual on the page. As us can see in the below screenshot we created the visual that is a pie chart.

Step 2: Populate the Visual

Drag the relevant data fields from the Fields panel and drop them into the appropriate areas in the visual. Once this is done, the pie chart will display the data accordingly.

Step 3: Duplicate the Page

Since we need at least two pages to apply a report-level filter, right-click on Page 1 and select Duplicate Page. This creates a duplicate page with the same visual setup.

Step 4: Apply the Report-Level Filter

Drag the desired data field like Country into the "Filters on all Pages" section. This applies the filter to all visuals across the report.

Step 5:

Choose the specific values from the data field such as Canada and France. These values will apply the filter across the entire report.

Step 6: View the Filtered Report

After selecting the filter values, the filter will be applied to the entire report including all pages. The same filter criteria will automatically apply to Page 1 from the duplicate page.

Note: To fully understand and use report-level filters, ensure we have at least two pages in our Power BI report.

How to Determine What Gets Filtered in Power BI?

In Power BI, we can easily find which fields and values are being filtered by checking the "Filters" panel. This panel shows the fields that have filters applied and we can hover over any filtered field to view the specific values that are included or excluded.

For example, if we’ve applied a filter to the Country field, we can see it listed in the Filters panel. By hovering over the filtered field, we'll see which specific values like Canada and France have been filtered. This helps us track what data is being displayed and ensures we’re analyzing the correct subset of data.

Difference Between Power BI Slicer and Filter

By understanding and applying Power BI filters effectively, we can ensure our reports focus on the most relevant data helps in making our analysis more efficient and impactful.