What is Filters in Power BI?
Filters in Power BI allow you to narrow down and focus on specific data within your reports and visuals. They allow you to define conditions that restrict the data displayed in visuals, tables, or other elements in your Power BI reports and help you analyze and present relevant data to your specific needs or queries.
Power BI Filters can be applied to single visuals, report pages, or across multiple pages or dashboards. Using Power BI Filters, you can segment your data based on different criteria, such as date ranges, categories, regions, or any other field in your dataset.
Table of contents
Key Takeaways
- Filters in Power BI help refine data analysis by including or excluding specific data points.
- Different types of filters, such as visual-level, page-level, and report-level filters, offer flexibility and control over data display.
- Slicers and drill-through filters enhance interactivity and exploration capabilities.
- Cross-filtering enables filtering one visual based on selections made in another, fostering data relationships.
- Understanding filter options and best practices empower users to utilize Power BI’s filtering capabilities for effectively insightful data analysis.
Types of Power BI Filters
You can apply Power BI Filters using the Power BI Filters pane or choosing slicer options directly on the report screen.
There are four standard types of filters you can create in the Power BI Filters pane:
- Visual level filters: These filters apply to a single visual on a Power BI report and are visible when you select a visual on the report canvas. They allow you to filter data based on specific dimensions or measures within the visual.
- Page-level filters apply to all the visuals on a Power BI report page. They allow you to filter data based on specific dimensions or measures and are effective when maintaining a consistent context throughout the page.
- Report-level filters: These are Power BI filters applicable on all pages in a report. They help create global filters that are applied to all the visuals and pages in the report. Report-level filters are useful for maintaining consistent filters across multiple pages or visuals.
- Drillthrough filters enable users to navigate from a summary to a detailed view by defining specific fields to drill through. They allow users to explore more granular data related to a particular data point. Using these filters, you can navigate from the other report pages and drill through to the focussed page.
There are also other filters you can apply to reports in Power BI. They include:
- Slicer filters: Slicers are visual elements that act as interactive filters. They provide a user-friendly way to filter data by allowing users to select specific values or ranges. Slicers can be applied to individual visuals or used as global filters across the report.
- Cross-filtering: Cross-filtering allows users to filter data in one visual based on selections made in another visual. It helps establish relationships between visuals and provides interactive filtering capabilities.
In the subsequent sections, we will cover how to add these filters in Power BI with step-by-step instructions.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Add Filters in Power BI?
Power BI Filters pane, visible along the right side of the report canvas offers convenient means to add filters in Power BI reports or dashboards.
Using this Filters pane, you can set filters at three different levels of the report including:
- Visual Level Filter
- Page Level Filter
- Report Level Filter
Let’s understand each of these filter levels with the help of examples.
Example #1 – Visual Level Filter
In this example, we will apply a visual level Filter to the Power BI Sales report using the Financials dataset with the steps provided below:
Step 1: Import the Financials dataset to Power BI using data connection and create visualizations by dragging the fields from the Fields pane onto the report canvas.
Here we have created two visuals, i.e., the Sum of Sales by Product and the Sum of Units Sold by Product.
As you can see, the fields in the visual are automatically included in the Filters pane under Filters on this visual heading.
Step 2: Choose the field you want to add as a visual-level filter from the Fields pane and drag it into the Filters on this visual area.
Here for the Sum of Sales by Product visual, we have selected the Segment field for the visual-level filter.
Once you have added the field to the Filters pane, Power BI, by default, will choose the Basic Filtering option from the Filter type and display the list of field values to choose from. You can choose the other options from the drop-down, such as Advanced filtering or Top N.
In this case, all the values for the Segment field are displayed for the basic filtering option.
Step 3: Select the field values you want to filter in the Filters pane. In this case, we have selected Enterprise and Government for Segment.
Notice that this visual filter does not impact the other report, i.e., the Sum of Units Sold by Product on the same Power BI report page.
You can also select the Require single selection to restrict only a single choice of field values in the Filter pane.
Example #2 – Page-Level Filter
In this example, we will apply the page-level filter in the Power BI report page using Financials dataset as per the steps highlighted below:
Step 1: Create Power BI visualizations by selecting the fields in the Fields pane and dragging them onto the report canvas. Here we have created 2 visuals, i.e., Total Sales per product and Total units sold per product.
Step 2: From the Data pane, select the field you want to add to the page-level filter and drag it to Add data fields here in the Filters on this page area
Here we have added the Segment field to the page-level filter. Once you add the field, Power BI will display the list of field values and filter type drop-down to choose from.
Step 3: Select the field values you want to filter and choose the Filter type from the drop-down.
Here we have selected Midmarket and Small Business and Filter type as Basic filtering
You will notice both visuals are redrawn to reflect the changes in the filter conditions.
Point to note: You can choose Advanced filtering as the Filter type to apply page-level filter
Example #3 – Report Level Filter Function
We will apply the report-level filter function in Power BI using the Financials as per the steps below:
Step 1: Create a visualization in Power BI by selecting the fields from the Fields pane and dragging them onto the report canvas. Here we have created the Sum of Discounts by Product visual.
Step 2: From the Fields pane, select the field you want to add as a new report-level filter and drag it into the Add data fields here in the Filters on all pages area.
We have selected the Segment field for the page-level filter. Note that once you add the field to the Power BI filter will display all the values and Filter type drop-down choices.
Step 3: Select the field values you want to filter and choose the Filter type from the drop-down.
The visuals on the active page, and other pages in the report, change to reflect the new filter.
Here we have selected Enterprise and Government and Filter type as Basic filtering
You will notice that this filter is applied to all the other reports on other pages in Power BI.
Additionally, you can use Power BI Filter function to apply filters to a column in a given context. The DAX expression for this function is as follows:
Important Things to Note
Important things to note about Power BI filters:
- Power BI filters can be applied to columns, rows, measures, or visual elements within a report.
- You can apply filters manually or set up default filters in the Power BI reports
- Power BI filters can be applied to a report’s columns, rows, measures, or visual elements.
- Filters can be saved as part of the report configurations, allowing for consistent and reusable filtering settings.
- Power BI Filters support Advanced Filter types where you can apply a range of filter conditions (e.g., equals, greater than), logical operators (And, Or), dynamic expressions (e.g., contains, starts with) or formulas, and multiple filters.
- Power BI Filters provide Top N Filter type functionality to limit the data to a specific number of records in a report or page.
- Publishing a report to the web doesn’t display the Filters pane, and hence, you may consider adding slicers instead of filters for publishing a report to the web instead of filters.
Frequently Asked Questions (FAQs)
The Power BI Filters can be removed using the steps highlighted below:
• Open your Power BI report or dashboard.
• Select the visual to make it active. All the filters applicable to the visual will be visible in the Filters pane
• Expand the filter card for the field on which you want to remove the filter
• In the Filters pane, click on the X button next to the filter you want to remove.
• Repeat these steps for any additional filters you wish to remove.
If the filters in Power BI are not working, it could be due to several reasons. Here are a few things you can check:
• Verify that the filter is correctly configured. Ensure that the filter is targeting the correct fields or columns and using the appropriate operators.
• Check if there are conflicting filters. Filters applied to the same field or column can override each other, so make sure there are no conflicting filters.
• Ensure that the visual or element you apply the filter to is connected to the correct data source and uses the appropriate fields.
• Check if there are any interactions or cross-filtering applied between visuals. Interactions can affect how filters are applied, so review the interaction settings.
Slicers and filters in Power BI have some similarities, but they differ in their usage and behavior:
• Slicers: Slicers are visual controls that allow users to select one or multiple values from a predefined list. They are typically used to filter data within a single report page or dashboard. Slicers provide an interactive way to slice and dice data, and they can be connected to multiple visuals simultaneously.
• Filters: Filters in Power BI can be applied to visuals, Power BI filters on all pages, or the entire report. They are used to narrow down the data displayed based on specific criteria or conditions. Filters can be manually set, or you can create dynamic filters using expressions and measures.
To use the advanced filter in Power BI, you can follow these steps:
• Select the visual or element to which you want to apply the advanced filter.
• Click on the filter icon associated with the visual or navigate to the Filters pane for the selected visual.
• Select the Advanced filtering option from the Filter type drop down.
• Specify the advanced filter conditions based on your requirements, such as equal to, not equal to, greater than, less than, etc.
• Apply the filter, and the visual will update accordingly.
Recommended Articles
This has been a guide to Power BI Filters. Here we learn how to apply different filters in Power BI to filter data with examples. You can learn more from the following articles –
Leave a Reply