Scope of Filter in Power BI Report

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
Scope of filter in Power BI report visuals

For a visualization tool, one of the important factors is to filter the data for a specific reporting requirement. Power BI gives you this ability by the Filters pane in the visualization. There are, however, three scopes of filtering, which you need to be aware of. In this short article and video, I explained how they work and what is their difference.

Video

Filters pane

Filters can be applied in the Filters pane in Power BI Desktop (or Power BI service) on the visuals of the report. In earlier versions of Power BI Desktop, the Filters area was an area under the visualization section, but these days, it has a separate pane for itself.

Filters pane in Power BI

This is the place that you can define all the filters for your visualization elements. These filters may or may not be visible for the users (depends on your configuration). Filters can be applied in three levels, or three scopes mentioned as below.

Visual-Level Filter: Only current visual

If you want to apply a filter on a specific visual only, then you have to click on the visual. After clicking on that visual, you will see the Filters pane has a section for Filters in this visual.

Filters on this visual in Power BI

By default, you will see the fields used in that visual under this section (in the example above both EnglishEducation and SalesAmount are fields used in the bar chart). You can change the filter condition for any of those, or add another field to the filters section by dragging it to the “Add data fields here”.

Add a new field as filter

Depends on the type of the field, you can apply the filter. Any filter applied in here, will only affect on this visual, but not other visuals in the page.

Applying filter for only one visual in the Power BI report

Page-Level Filter: All visuals on the current page

If you want to apply filters on all the visual in the page, it is much easier to set the “Filters on this page”. Using this option. all the visuals in that page will get filtered.

applying filter on all visuals in one page

unlike the visual level filter, there is no field in the page level filter by default. You have to drag and drop fields into this area to start filtering. Filters applied here will not apply on other pages, they only effect this current page.

Report Level Filter: All visuals on all pages

The most wide range of filter to be applied is the filter you apply on all pages. This will filter all the visuals in all pages of the current report (meaning the current *.PBIX file).

page level filter in Power BI

As you can see in the example above, the filter applied on “all pages” simply filters all the visuals in both pages of the sample report.

How to know what is filtered?

A question I get commonly asked in my courses is that; “once the data is filtered, how do we know what is filtered? especially for the users it is important to know that”. The good news is that Power BI has an option to show you what is filtered, when you hover the mouse on the little filter icon on top of every visual;

Filters affecting this visual

This will show the filters applied from anywhere (Visual, page or report-level), unless they are hidden (which I explain in another article).

Consider Filtering in the Power Query Editor

Using Visual-level filter and the Page-level filter both are common. However, If you are applying a Report-level filter (Filter on all pages), I strongly recommend you to consider an alternative. When you filter the data at the report level, this means you are not using the part that you filtered out. However, that part of the data is still there (in-memory), and affect the performance and the size of of the model.

I would strongly suggest you to consider filter the data in Power Query Editor (or in the source). When you filter the data this way, the unwanted data will not be loaded into Power BI at all, and you will have a much better performing model.

To do this filtering, you need to go to the Transform Data from the Power BI Desktop, and then in the Power Query Editor window, you can click on any of the column headers and apply the filter you want.

Filtering data before loading into Power BI

Summary

There are three scopes of filtering for the visuals in Power BI; visual-level, page-level, and report-level. Depends on what items you want to filter, you can choose any combination of the above. If you are applying a report-level filter, I strongly recommend considering filter the data before loading it into Power BI. This can be done using Power Query Editor or filtering data from the source system.

There are more settings for the filters in Power BI, things such as locking filters, hiding them, and how to configure the look and feel of the filters pane. I’ll explain those in other articles in the future.

FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply