What are Power BI filters?
Filters in Power BI sort data and information based on some selected criteria. That is, you can select particular fields or values within fileds and view only the information related to that. For instance, if we are only interested to see the sales records of our KANA business unit in 2008, you will apply filters on BU and year to select the data you wish to see.
Types of Filters in Power BI
Power BI offers three options to filter a report, data, and visualization: visual-level filters, page-level filters, and report-level filters.
- Report-level filers: These filters are like universal filters. All pages and visuals will be affected, regardless of what you are looking at.
- Page-level filters: These filter will only affect the data in the given page, which makes them useful for creating pages that focus on particular subsets of your data.
- Visual-level filters: These filters work only on the single selected visual. These are the most granular filters you can apply to your data, and they operate within the context of both the page-level and report-level filters, which means that a visual-level filter cannot override them, nor can they be programmed to filter data on other visuals.
Demonstration on applying filters in Power BI Desktop
The dataset used for the demonstration is Microsoft's sample dataset Northwind. Northwind is a fictitious specialty foods export/import company. The database captures all the sales transactions that occurs between the company (i.e. Northwind traders and its customers as well as the purchase transactions between Northwind and its suppliers).
The following three screenshots are the three pages in the report:
Page 1: Total sales by year
Page 2: Category
Page 3: Order Details
You can apply the different levels of filters in the Filter Pane:
Report-level filter
First, let us apply a report-level filter that makes the whole report to only show data from the year 1998:
In the Fields pane on the leftmost side of the screen, choose "Year" from OrderDate's Date Hiearchy then drag it to the "Filters on all pages" part of the Filters pane. Then , make sure the filter type is "Basic filtering", then select 1998.
After you have applied the report level filter, you can already see a difference in the current page. The column chart Total Sales by Year only shows data from the year 1998. The other two pages will also only show data from 1998:
Page 2:
Page 3:
Page-level filter
We will try to apply a page-level filter on the second page "Category". Drag CategoryName from the fields pane to "Filters on this page" on the Filter pane. Then, select every category name except for Beverages, Condiments, and Confections.
You shall then see that all the visuals on this page will not include the categories: Beverages, Condiments, and Confections. However, all other pages will still include all categories.
Visual-level filter
We will test out the visual-level filter on the pie chart of the same page "Category". Select the pie chart, then you should notice that the Filters pane will have a section called "Filters on this visual", which already has two filter cards: "CategoryName" and "TotalSales". We will apply a visual-level filter to the Category Name, so the pie chart does not include Dairy Products .
After the filer has been applied only to the pie chart, the column chart and the table will still include the category
Diary Products". The rest of the pages will also still include "Dairy Products.