Power BI Drill through Filters are used to drill through particular field, or we can say, apply filters on reports at multi-level. In this article we will show you, How to create Drill through Filters in Power BI with example.
How to create Drill through Filters in Power BI
To demonstrate the drillthrough reports in power bi, we are going to use the Countries hierarchy. Please refer Create Hierarchy article to understand the steps involved in creating a hierarchy.
As you can see from the below screenshot, we created a Column Chart, by adding the Country hierarchy in the Axis Field, Sales amount as the Value field, and Order Quantity as the Color Saturation.
Under the Filters section, you can see the list of available filters in Power BI. You have to use the Drillthrough Filters section to create a filter at multi-level.
Let me drag and drop the Country from fields section to the Drillthrough Filters.
Expand the Country to see the filter types.
For the demo purpose, let me select United Kingdom. From the below screenshot you can see that, the Column chart is displaying the Sales for the United Kingdom.
TIP: By default, you can only select One field (Country) at a time.
Let me drill down to next level. I suggest you to refer Drill Up and Drill Down reports article to understand the Multi-level reports.
Now you can see the Sales by State (level 2). If you notice closely, it is displaying the sales for England because, this is the only state available for the United Kingdom
Let me go to the another level. Now you can see the Sales by Cities present in the United Kingdom (level 3).
You can drill down further to see the Sales by Postal Codes present in the United Kingdom
This time we are selected Australia, and went all the way down to Postal Codes level. From the below screenshot, you can see the Sales by Postal Codes present in Australia
Let me remove the existing Drill through filter, and drag and drop the Sales Amount from fields section to the Drillthrough Filters. Next, expand the Sales Amount to see the filter options.
Let me select the Sales Amount as 3399.99
Now you can see the States whose Sales Amount is at least 3399
Now you can see the Postal Codes whose Sales Amount is at least 3399
Let me Sort them by Sales Amount