Power BI Drill through Filters are useful to drill through a particular field, or we can say, apply filters on reports at multi-level. Let me show you how to create Drill through Filters in Power BI with an example.
How to create Drill through Filters in Power BI
To demonstrate the Power BI drill through filters report, we are going to use the Countries hierarchy. Please refer to 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 Power BI Axis Fields. And added the 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 Power BI Drill through Filters section to create a filter at multi-level.
Let me drag and drop the Country from fields section to the Power BI Drill through Filters section.
Expand the Country to see the filter types.
For the Power BI Drill through Filters demo purpose, let me select the United Kingdom. From the screenshot below, you can see that the Column chart is displaying Sales for the United Kingdom.
TIP: By default, you can only select One field (Country) at a time.
Let me drill down to the next level. I suggest you 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 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 in Australia and went all the way down to Postal Codes level. From the screenshot below, you can see the Sales by Postal Codes present in Australia.
Let me remove the existing Power BI 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
