Power BI – Drill through Filters

You may designate a destination target page in your Power BI report that focuses on a particular entity, such as a supplier, client, or manufacturer. Right-clicking a data point on one of the source report pages will take readers to the target page, where they can read the information filtered for that context. Power BI Desktop or the Power BI service both allow you to set up drill-through in your reports.

Drill through Filters

Get the required Data Tables from here, viz. DimDate, which contains the date of sales sorted out according to month, day and year, etc. DimCustomer, to get information about customers such as their first name, last name, gender, marital status, etc. DimGeography to get the demographic details of the customer. FactInternetSales is a dummy model of sales through the internet and related customer data. and FactResellerSales to extract the same information about the resellers. Select DimDate, DimGeography, FactInternetSales, and FactResellerSales hierarchical data tables. And load the data model.

The data model will be loaded in less than a minute.

Steps to Set Up Drill through Filters

Set Up Drill through Target Pages

First, decide on what basis you want to drill through. For example, here we have two pages at the start that are the target pages. On page 1 Drill through based on the calendar year, then add the calendar year field on the page named Home to add a Drill through the filter in the visualization, then automatically a back button will be created to explore within the pages. Similarly, on page 2 Drill through is based on the French country region. 

• For achieving Drill through we need at least 2 target pages apart from the “Home” or navigation page.  Create a target report page with the desired graphics for the type of entity you’re providing Drill through for in order to set up Drill through. 

Select fields for page 1:

For page 1, we have selected “Calendar Year” from the Dim Date Table, “Sum of Sales Amount” from the “Fact Internet Sales” table, and “Sum of Sales Amount” from the “Fact Reseller Sales” Table to create a single model.

Along with it, we can move on to other information as well, “Sum of Order Quantity” and “Sum of Freight” in card visuals.

Select fields for page 2:

For page 2, we have selected “French Country Region Name” from Dim Geography Table, “Sum of Sales Amount” from the “Fact Reseller Sales” table, and “Sum of Sales Order Line Number” from the “Fact Internet Sales” Table to create a single model.

Also, convert it into the matrix visualization.

We can modify the table from the visualizations section.  Along with it here we can move on to other information as well, “Sum of Total Product Cost” from the Fact Reseller Sales table in card visuals.

Pass Filters by Column

The Drill through target page can be configured to receive all applicable filters. For instance, you may choose only a specific product category and the visuals that are sorted to that category, then choose Drill through on a source. The temporary filters you set to the source visual are likewise applied to the Drill through target page when you later Drill through on a visual on a source page. These transient filters are displayed in italics in the Visualization pane’s Drill through the section. Go to the destination page to keep all of the applied filters. Set Keep all filters to On in the Drill through a section of the Visualizations window.

• Drag the field for which you wish to allow Drill through into the Drill through well on that Drill through target page’s Build visual part of the Visualizations pane. When you add a field to the Drill through filters well, Power BI automatically creates a back button visual. That visual becomes a button in published reports. Users who view your report in the Power BI service using this button on the target page to get back to the original source report page from which they came.

Drilling through page 1:

Drag the field Calendar Year for Page1 Drill through,

The back button is an image, so you may swap it out with any other image you like. It still functions as a back button, allowing users to return to the report’s original source page. Only the Power BI Desktop, not the Power BI service, allows you to add your own photos.

In the “Home” page drag the “Calendar Year” viz the field or column which is added to Drill through the respective page. For example, here we want to drill through the 2005 field. Right-click on the 2005 field and select the Drill through to page 1.

Data viz. Sum of Sales Amount by Calendar Year visual, Sum of Order Quantity, and Sum of Freight of the 2005 calendar year can be seen individually as follows.

Let’s set the Drill through to the 2013 calendar year field.

Data viz. the Sum of Sales Amount by Calendar Year visual, Sum of Order Quantity, and Sum of Freight of the 2013 calendar year can be seen individually as follows.

Drilling through page 2:

Similarly, Drag the “French Country Region Name” from the Dim Geography table for page2 Drill through.

In the “Home” page drag the “French Country Region Name” viz the field or column which is added to Drill through the respective page. For example, here we want to drill through the France field. Right-click on the France field and select the Drill through to page 2.

Various Information like the Sum of Sales Amount, Sum of Sales Order Line Number, and Sum of Total Product cost related to France’s country Region can be seen individually.

The steps below can be used to create a back button using your own image:

• Choose Image from the Insert tab in Power BI Desktop. Locate your image after that, and then put it on the Drill through target page.
• On the Drill through target page, choose your replacement image. Set the Action slider to “On” under the Format image window, and then set the Type to Back. Now, your image serves as a back button.

Now, when a user right-clicks a data point on one of your report’s other source pages, a context menu with a Drill through the option to the destination page appears. The target page is filtered to provide details about the data point that the report reader right-clicked on when they choose to Drill through. Even if you could accomplish this with tooltip pages, the tooltip wouldn’t seem to be functioning properly, making the experience strange. This is why we advise against doing it using tooltips.

Pass Filters by Measure

You can add a measure or a summary numeric column to the Drill through the area in addition to passing all filters to the Drill through the target page. To use the Drill through the field, drag it to the target page’s Drill through the card. 

Drilling through page 3:

Set up another Target Page 3 here from the Dim Customer table. Select “Customer Key”, “Gender”, and “Marital Status” from it, to create a single model. Along with this, we can move on to other information as well, “Sum of Sales Amount” and “Sum of Order Quantity” in card visuals.

When a field is used in the Value area of a graphic, you can drill through to the page after adding a measure or summarized numeric column. Drag the “Gender” column on the “Home” page and create a “New Measure”. Here we have created a measure to count the number of Females customers in the Dim Customer table.

Measure = COUNTROWS(FILTER(DimCustomer,DimCustomer[Gender] = “F”))

It will create a Measure column in the Dim Customer table, based on which we can Drill through.

Drag the Measure field to set up the Drill through.

Drag the Gender field in the “Home” page and select “F” to count the number of Female customers and their details as set up by the measure and Drill through the respective page.

The respective Female details will be as shown below,

Setting up Drill through in your reports is as simple as that. It’s a fantastic approach to obtain a more detailed look at the entity data you choose for your Drill through the filter.