Setting up Drill Through in the New Power BI Report User Interface

By:   |   Updated: 2023-08-17   |   Comments (2)   |   Related: > Power BI


Problem

Earlier this year (2023), the Power BI team released new updates to the Power BI desktop user interface, totally changing how we usually use features in the Power BI desktop. Among these updates was the new way to set up drill-through functionality in Power BI reports. Before, drill-through fields were usually dropped within the "Add drill-through fields here" of the visualizations pane. But now, this is done differently since the old visualizations pane no longer exists. In short, many Power BI developers/analysts are not so sure how this works nowadays, and for those new to Power BI, the old articles will not help.

In this article, I will demonstrate how to implement drill-through in Power BI reports using the new user interface.

Solution

To demonstrate, I have created a report page, as seen in the image below.

Sample main page Power BI report

The plan is that when I choose a country in the "Top 10 Countries by Gold Medals Won" chart, I want to be able to drill-through to a details page to view more details about the selected country. Some business requirements might also specify that you create drill-through functionality on card (summarized) visuals, too. We will look at achieving both in the new Power BI report UI.

The following step-by-step approach simplifies how to set up drill-through.

  1. Create the main report page and the details page.
  2. Set up the drill-through details in the format pane of the details page.
  3. Add category or summarised fields to the drill-through well settings accordingly.
  4. Test your set up.

Step 1: Create the Main Report Page and Details Page

In this demo, I will use the report page in the image above as my main report page and create a table visual, that will include some details from the image below as my "Details page."

Sample table visual  on details page in Power BI report

Step 2: Set Up the Drill-through Details in the Format Pane of the Details Page

In the new Power BI desktop UI, navigate to the details page you created. This is the page you want to be able to drill through to.

While in the drill-through page, click on any empty space within your report canvas. This will allow you to view the page format pane, as seen in the image below.

Format pane in PBI

If you cannot view the page format pane after clicking on an empty space on your details page canvas, you can right-click on the empty space and click "Format canvas," as seen in the image below.

Alternative way to navigate to the format canvas in Power BI new UI

Or you can also follow the steps on the image below to help you navigate to the page format pane.

How to navigate to the format canvas in Power BI new UI

Once you can view the page format pane, expand the "Page Information" section, as seen in the image below.

Page Information section of the PBI format pane

Next, change the option in the Page type subsection to "Drillthrough," as seen in the image below. This should automatically activate the "Keep all filters" functionality, which helps to maintain all applied filters cascaded from the Main Report page to the Details page when a drill-through is selected.

How to switch the page type on the page information section in PBI format pane

Step 3: Add Category or Summarized Fields to the Drill-through Well Settings Accordingly

The image below compares the old drill-through and the new one.

PBI old drill-through well vs new UI drill-through well

In the new drill-through well, let's add a field from the Main report page to the Details page to display its details. This can be a category field or a summarized card visual (aggregated filed value).

For example, on the Main Report page, we have the country names in the charts, the "Top 10 Countries by Gold medals won" chart, and on the Details page, we also have the country names within the table visual. So, we can add the "country" category field into the "Drill through from" subsection of the Page Information.

To do this, click the "+Add data", as seen in the image below. Select the field you want. For this demo, I will select the "Country" categorical field.

Adding fields to the drill-through well of the new PBI UI

Since country is a categorical field, it will automatically show in the "Drill through when" section as "Used as category."

Diagram showing the drill through when sub-section of the new PBI UI

If we needed to apply the drill-through on a numerical field like the summarized values on a card visual, we would need to follow the same steps earlier but instead select the numerical (summarized) field rather than the categorical field.

Step 4: Test the Set Up

The last step is to test the drill-through configuration. To do this, go back to the Main Report page and right-click on a single country point (as seen in the image below) or hover over a single country point on the charts, select drill-through, and choose the details page you need to drill-through to. In this demo, we will drill through to the Details page. See the image below.

How to navigate to a drill-through page from a report main page sample

In the image above, I right-clicked on "United States," selected "Drill through," and clicked on "Details page." This drills through to the Details page filtering out only United States information, as seen in the image below.

Image showing filtered drill-through for United States on a details page from a drill-through page

Note: A back navigation button is automatically created once you set up the Details page in Step 3.

Also, if you added a numerical field to the fields well to a card visual in Step 3, you can drill through from the card visual to the Details page by right-clicking on the card visual in the Main Report page, as seen in the image below. However, note that the cascaded filter for the card visual will not be applied to the Details page as in the case of categorical fields.

How to navigate to a drill-through page from a Card visual on report main page

In this article, I have demonstrated how to set up a drill-through in your report using the new Power BI user interface. See the Next Steps below if you need further study or investigation on what drill-throughs are all about.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelorís and masterís degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

View all my tips


Article Last Updated: 2023-08-17

Comments For This Article




Friday, September 1, 2023 - 8:58:53 AM - Kenneth Back To Top (91524)
Good to hear you find this article helpful. I appreciate the feedback too, thanks.

Friday, September 1, 2023 - 2:13:53 AM - Patj Back To Top (91522)
I wish I'd had this tip a month or two ago - took me forever to find how to do this.