How to Use Filters in Power BI (Step-By-Step Tutorial)
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Power BI filters help you see the data you want to focus on.
For new Power BI users, setting up filters for reports might be a bit confusing especially that there are different types of filters.
So what are filters in Power BI? How do you apply them?
In this article, we’ll show you how to use filters step by step.
Let’s get started! 👍
*This tutorial is for Power BI Online (also called "Power BI Service").
Table of Content
Introduction to Power BI Filters
A Power BI filter is a feature in Power BI where only selected data are shown on a visual, page, or report.
Basically, there are 4 types of Power BI filters:
- Visual-level filters
- Page-level filters
- Report-level filters
- Drillthrough filters
The first 3 ones are easy to understand since they are filter levels. The fourth one, however, isn’t that familiar.
Drillthrough filters, in simple words, create destination report pages for a specific entity or element.
In this article, we’ll walk you through setting up each one.
By the way, Power BI recently updated the filter experience in Power BI.
If you visit your old reports and edit them, Power BI will inform you that you are using the old filter pane and that you should update now.
With Power BI’s new filters pane, you can now:
- Add/remove fields to filter on
- Format and customize the filters pane
- Change the default state (open or collapsed) of the pane when a consumer views the report
- Hide filters or the whole pane from the consumers
- Lock filters so consumers can’t edit them
Before we go through the steps, you should know that:
- Filters can only be added using the reports builder (as when editing or creating a Power BI report).
- The dataset used in this tutorial came from the free retail analysis sample.
Adding a Visual-Level Filter
A visual-level filter affects only a single visualization data and calculation. It doesn’t affect any of the filters on the page.
To start, we have this ‘Last Year Sales and This Year Sales by FiscalMonth’ area chart.
Basically, it shows a running comparison between the sales this year and last year by fiscal month.
Selecting the chart would automatically show all the fields on the filters pane that can be filtered.
To add a filter on a field already being used by the visualization, click the down arrow to expand the options and select the specific filters you want.
For example, let’s filter this chart by FiscalMonth specifically in the first 6 months of the year:
You can see how the visual changed and only showed the values from January to June.
Here’s a question:
What if you would like to add a visual filter with a field that’s not on the visual? Like in this case, filter the visual by store name or district manager?
To do that, simply drag that field from the fields name to the filters pane inside the ‘Add data fields here’ bucket.
It’s important that you “drag” the field over to the bucket and not click the checkbox. Doing the latter would add it to the values or axis of the visual depending on the data type.
Let’s try adding ‘District Manager’ to the mix so we could filter the visual according to a specific manager or managers:
With this, ‘District Manager’ is only added as a filter and not to the visualization.
After dragging the field, the options are automatically opened. Let’s filter the visual according to:
- Allan Guinot
- Andrew Ma
The area chart now reflects the values by month related only to Allan Guinot and Andrew Ma.
Adding a Page-Level Filter
Page-level filters are similar to visual-level filters but on a page-level.
The steps in adding one are also similar.
Let’s say we have this 2 visuals on the canvas:
- ‘Last Year Sales and This Year Sales by FiscalMonth’
- ‘This Year Sales by StoreNumberName’
To add a page-level filter, make sure no visual on the canvas is selected.
If so, simply click on an empty space on the canvas so only the page-level filters and report-level filters are shown on the filters pane.
Let’s add ‘District Manager’ as a page-level filter.
Drag the ‘District Manager’ field on the bucket below ‘Filters on this page’:
Automatically, the filter options for that field will open.
Let’s select ‘Allan Guinot’ to only show data relevant to him:
Immediately, the chart will only show data connected to Allan Guinot.
Adding a Report-Level Filter
If page-level filters affect all visuals within the report page, a report-level filter affects every visual on every page on that report.
Adding it easy and you may already have seen the bucket in the previous section.
To demonstrate, we’ll be applying a report-level filter on the free retail analysis sample.
After opening the retail analysis sample report (any report page) in edit mode, you’ll immediately see all the current filters being applied on that page and/or the pages.
Let’s add a ‘District Manager’ report-level filter on this sample.
Again, drag the ‘District Manager’ field to the filters pane. But this time, place it on the bucket below ‘Filters on all pages’:
Then, let’s select ‘Andrew Ma’ and see how it affects the entire report page:
After selecting ‘Andrew Ma’, all the visuals on the report showed data relevant to that specific district manager. Even the map zoomed in to show only the new stores which have something to do with Andrew Ma.
Adding a Drillthrough Filter
A drillthrough filter allows you to dig into a specific element by creating destination report pages where the visuals are filtered according to that element.
For example, the retail analysis sample has a store sales overview report. However, you would like to have a detailed report about how a chain is doing in terms of:
- Open store count
- Sales this year
- Sales per category
To create a drillthrough filter, you first have to create another report page within that report and create visuals about the data you would like to show:
To add a ‘Chain’ drillthrough filter, drag the ‘Chain’ field to the drillthrough bucket found on the visualizations pane:
After that, a “go back” icon will appear on the upper-left corner of that report page:
To test out the new drillthrough function, visit the “Store Sales Overview” report page. Then, select a visual that has the same element as the drillthrough filter.
Clearly, the ‘This Year Sales by Chain’ visual is what we’re looking for. Right-click on any of the element and you’ll see a ‘Drillthrough’ option.
Hover your mouse over it and select the name of the destination report page:
Clicking that will bring you to a filtered version of that report page:
Amazing, right? 👍
As you can see, Power BI filters are powerful features that would let you filter data at any level that you need. The best thing? You can use all of them at once!
At first, it may seem a bit confusing to you especially the drillthrough filter. But as you practice more and more, you’ll find it very easy to execute. 😊