Drillthrough Feature in Power BI

Power BI's drillthrough feature empowers users to navigate from a summarized data point in one report page to a more detailed, focused view on another page, all while maintaining the context of the selected data. This functionality significantly enhances the interactivity and analytical depth of Power BI reports, allowing for a more granular exploration of information.

Let’s we have a page named “Source Page” in the Power BI. In this page we have a column chart. And two slicer one for Year and another one for Country slicer.

Drillthrough in Power BI Desktop

Now we want to dig down more in the GDP value, and use the filters applied on GDP to get this number to apply the same filter on other page.

Let’s create a target page. On this page we have added a table visual and add the fields coming from the Countries table and format it.

Drillthrough in Power BI Desktop

Select the canvas of the page to format the page and from the Page Information. Select the Drillthrough from the Page type dropdown.

We can choose Keep all filters toggle to on and off. It states that whether we want to keep all the filter applied on the visual or just the filters applied on the fields listed on the drillthrough fields.

Select the field or fields which we want to use as a drillthrough to reach on this page and applied the same filters. We can add Drillthrough fields as columns or measures. The Keep all filters toggle is mandatorily in on state when we have selected a measure in the drill through fields or a column as a summarized column.

When we add the drillthrough fields on the drillthrough page, Power BI automatically adds a Back button on the top-left side on the canvas, with action Back. That visual becomes a button in published reports. Users who view our report in the Power BI service use this button on the target page to get back to the original source report page from which they came. We can also delete or customize this back button or create a new one.

Note: • Please note that it is not required to use the drillthrough fields on the Target page, as drillthrough implements the filter applied on the source page on drillthrough field or its visual filters on the Target page.
• Generally, in practical implementation the drillthrough pages are hidden from the end users, to hide a page right click on the page from the page tab, and then select Hide Page. So, the end users access the drillthrough pages from the visuals on the other pages not directly.

Now we have successfully setup the drill through feature, to execute it let’s go back on the Source page.

Now right click on the drillthrough field and then select the Drillthrough page.

Drillthrough in Power BI Desktop

In the image we have selected the Germany country and then select the Target Page our destination page for the drillthrough. Here we can one more thing we have selected 2021 from the Year slicer which also implement as a filter on this column chart visual.

Drillthrough in Power BI Desktop

Here, the filters Country is Germany and Year is 2021 are known as transient filters as these are not permanently applied on the source visual that is shown it is shown as in italics.

Note: Report consumers might not be aware that drillthrough is supported by a visual. To promote visibility, we can add a button that performs drillthrough.

Set up a drillthrough button To set up a drillthrough button, we first need to set up a valid drillthrough page within our report.

Steps to Add a Drill-Through Button in Power BI

Note: • We can set up drillthrough action on the shape or image also.
• We can also conditionally assign the drillthrough destination page.

The button remains disabled until drillthrough becomes a valid action, which is the case when we are interacting with a visual that can navigate to the drillthrough page. When the Drillthrough action is selected, Power BI navigates to the drillthrough page, propagating appropriate filters.

Drillthrough in Power BI Desktop

Because the drillthrough button has two states, enabled and disabled, we see two tooltip options.

If we leave the tooltips boxes blank, Power BI automatically generates tooltips. Those tooltips are based on the destination and drillthrough fields. In the image above shows an autogenerated tooltip when the button is disabled.

Let’s publish the report in the Power BI service and test the drillthrough button.

At first the button is shown in the disabled state, as shown in the image below:

Drillthrough in Power BI Desktop

Once I select a data point of the drillthrough field then the button is enabled as shown in the image below:

Drillthrough in Power BI Desktop

When we click on the button, it will reach us the destination page, with all the applied filters.

Drillthrough in Power BI Desktop

Note: We can customize the formatting of the button according to the state i.e. Default, On hover, Press, and Disabled.

Tip: We can add a Country field in the Card visual on the drill through page as it is a text field it automatically takes the first name on the alphabetically sorted. And a text box with name Selected Country: at the top of the it. We can pass the filter from the source page to this drill through page always there is only country selected which is passed as filter. So, we can see our selected country name in the card visual. Please note we select two counties and try to reach the drillthrough page, the drillthrough option is not available. 😊

Drillthrough in Power BI Desktop