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 a multi-level. Let me show you how to create Drill through Filters with an example.

To demonstrate the Power BI drill through filters report, we are going to use the Countries hierarchy. Please refer to the 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. The Country hierarchy includes the country name, State, City, and Postal Code. Next, we added the Sales amount as the Value field and Order Quantity as the Color Saturation.

How to create Drill through Filters in Power BI

Under the Filters section, you can see the list of available filters. You have to use the Power BI Drill through the Filters section to create a filter at multi-level.

Drag Fields 2

Let me drag and drop the Country from the fields section to the Power BI Drill through the 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 displays Sales for the United Kingdom. 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 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 of Drill through Filters in Power BI. Now you can see the Sales by Cities present in the United Kingdom (level 3).

Drill through Filters in Power BI 7

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

Select any Country Name to apply on chart 8

This time we were selected in Australia and went all the way down to the Postal Codes level. From the screenshot below, you can see the Sales by Postal Codes present in Australia.

Select Country field 9

Let me remove the existing Power BI Drill through filter and drag and drop the Sales Amount from the 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

Choose Sales value 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

Sorting in Drill through Filters in Power BI 15

The following are the remaining types of Filters with a practical example.

  1. Slicer
  2. Basic Filters
  3. Top N Filters
  4. Filters on Measures
  5. Page-Level Filters
  6. Report Level Filters

Comments are closed.