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.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started! 👍

*This tutorial is for Power BI Online (also called "Power BI Service"). If you have Power BI Desktop, then click here and go to the online version.

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.

Kasper Langmann, Co-founder of Spreadsheeto

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

Awesome! 👍

Before we go through the steps, you should know that:

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.

An area chart with a running comparison between the sales this year and last by fiscal month

Selecting the chart would automatically show all the fields on the filters pane that can be filtered.

All the fields that can be visually 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:

Adding a visual filter on an existing field

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.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s try adding ‘District Manager’ to the mix so we could filter the visual according to a specific manager or managers:

Adding ‘District Manager’ as a filter data field

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’
2 types of visuals on the canvas for page-level filter sample

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.

Kasper Langmann, Co-founder of Spreadsheeto

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 selectAllan Guinot’ to only show data relevant to him:

Page filtered according to data relevant to Allan Guinot

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.

Kasper Langmann, Co-founder of Spreadsheeto

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’:

Drag the ‘District Manager’ field to the report-level filter bucket

Then, let’s selectAndrew 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:

A sample destination report page for a drillthrough filter

To add a ‘Chain’ drillthrough filter, drag the ‘Chain’ field to the drillthrough bucket found on the visualizations pane:

Adding the ‘Chain’ field to the drillthrough filter bucket

After that, a “go back” icon will appear on the upper-left corner of that report page:

The back icon after adding a drillthrough filter

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.

Kasper Langmann, Co-founder of Spreadsheeto

Hover your mouse over it and select the name of the destination report page:

Showing the drillthrough option to the destination report page

Clicking that will bring you to a filtered version of that report page:

Amazing, right? 👍

Conclusion

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. 😊

Kasper Langmann, Co-founder of Spreadsheeto