Drill through Filters in Power BI

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.

Drill through Filters in Power BI 1

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.

Drill through Filters in Power BI 2

Let me drag and drop the Country from fields section to the Power BI Drill through Filters section.

Drill through Filters in Power BI 3

Expand the Country to see the filter types.

Drill through Filters in Power BI 4

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.

Drill through Filters in Power BI 5

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

Drill through Filters in Power BI 6

Let me go to another level. Now you can see the Sales by Cities present in the United Kingdom (level 3).

Drill through Filters in Power BI 6

You can drill down further to see the Sales by Postal Codes present in the United Kingdom

Drill through Filters in Power BI 8

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.

Drill through Filters in Power BI 9

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.

Drill through Filters in Power BI 10

Let me select the Sales Amount as 3399.99

Drill through Filters in Power BI 11

Now you can see the States whose Sales Amount is at least 3399

Drill through Filters in Power BI 13

Now you can see the Postal Codes whose Sales Amount is at least 3399

Drill through Filters in Power BI 14

Let me Sort them by Sales Amount

Drill through Filters in Power BI 15

Comments are closed.