Drillthrough on All Columns and Measures in Power BI

Here’s a quick Power BI tip for you: Setting up drillthrough so that it never breaks! What do I mean by this exactly? Well, since Power BI recently came out with Personalized Visuals, end-users in Power BI Service can change visuals to their liking. Users can switch any field that they desire to show different data. Imagine that you set up drillthrough from one page to another using a certain measure or category and then the end-user changes the chart to use other categories and measures. Your drillthrough action will now not work as expected. This quick trick is going to allow your drillthrough to *always* work, no matter which fields the user decides to visualize!

As you may know, you can place as many categories and measures as you want in the drillthrough well. This enables page 1 to navigate to page 2 whenever any of the categories/measures are interacted with on page 1. You can simply throw in all of the potentially used categories and measures in the drillthrough well, but this isn’t a great solution. Your data model may change, you might add some new measures and forget to add them, you might accidently remove some from the well and forget to add them back, etc. This method will allow you to drillthrough via a single category that will apply to all visuals. Without further ado, let’s get into it.

We’re going to start with a report already set up with two tabs, a Summary tab and a Detail tab. I’ve also created a drillthrough button that shows when the drillthrough action is allowed. This allows us to easily see if the drillthrough is working without having to actually navigate to page two. Right now, my set up looks like this:

We currently don’t have anything in the drillthrough fields on the Detail tab, so the Drillthrough button can’t be enabled at this point.

Next, we need to create a new table using the “Enter Data” option. The table will be disconnected from any other table and will simply hold a dummy value to initiate the drillthrough. I am putting a single space in the cell like such:

I’m naming the table “Drillthrough” and providing a single column called “Drill.” Again, the cell in row 1 has only a single space character. You can put any value in here that you like, but I choose to use a space because it won’t show up anywhere when we use this field in visuals.

Next, throw this drill column into the drillthrough fields on the Detail tab (page 2). It is very important that you enable the “Keep all filters” toggle. This is the key to this trick.

If you are using a drillthrough button like I am, make sure to set the action to drill through to the other tab. If you are drilling through by right-clicking on a visual, you can skip this step.

The last thing we need to do is add this same Drill field to any visualizations that we want the drillthrough action to work for. Where you add this field will differ by visualization. On our page, we have a column chart, donut chart, and tree chart, but this can work with any of the other visuals as well.

For the column chart, I will simply throw the Drill column into the Legend well. For the donut and tree charts, you can add Drill to the Details well. This doesn’t change how the data is displayed as long as you have something in the Legend or Group wells of these visuals.

Now we can test if drillthrough is working. I’m going to click on each of the charts and see if the Drillthrough button shows as enabled. See below gif:

The really cool part is that we can see that our filters from the visuals are applied on the Detail tab because of the “Keep all filters” option. In the above gif, we initiated the drillthrough on the White ColorName category and we can see that this filter was applied on the Detail tab!

Now, if your end-users alter the visualizations in any way, they will still be able to drillthrough to the Detail tab as long as they leave the Drill column in the visual. Moving forward, I might rename the Drill column to Drillthrough DO NOT REMOVE or something similar to get the point across.

And that’s the whole trick! I hope you enjoyed. If you would like to access the data behind this report, make sure to check out my Training courses here. All memberships grant you access to a live SQL Server database to practice Power BI!