It is a pet peeve of mine and it drive me nuts. I hate it when someone tries to fit as many visuals on the canvas as humanly possible. It is cluttered, ugly, and hurts the performance of your report. But how do we get around this issue? The answer is simple! Add a drill through to your report!

I will be honest – I was confused by the initial setup like you might be. The setup is a little intimidating at first, but once you understand how to configure the drill through, it is easy to implement. Like what I said at the end of my article about bookmarks in Power BI, you will probably get things wrong the first time. However, with some guidance, I hope you will be able to implement them with ease!

Preparing Your Report

Generally, this starts with a basic report page with a goal. Right now, I have a Sales Data report page that has a lot of information. However, I want to drill down on a manufacturer and learn more about the products being sold. If you look at our current Sales Data tab, you will note that there is some product information, but not the context I want:

Screenshot of the Sales Data report page with a line chart comparing year over year of sales data, revenue by state on a map, a table of the top 25 products and their manufacturer by revenue, and a bar chart of quantity sold by manufacturer.
Current state of the Sales Data report

Our first challenge is to create a meaningful drilldown page that shows information by manufacturer. In my scenario, I wanted to see the top products by both revenue and quantity. I also wanted to see product segments and have the ability to filter by category. The result is a drilldown page that looks like this:

Screenshot of the manufacturer drilldown that shows two tables that return the top 20 products by revenue and quantity respectively. There is a donut chat showing distribution of revenue by segment and a tile slicer for product category.
Manufacturer drilldown report page

You will note that I did not place a slicer for manufacturer on the canvas. This is because the drill through will handle the filtering for us. We will tackle that next.

Add Drill Through Logic

With our detailed report created, we need to enable the drill through functionality. On the report page, we must add the field we want to use for the drill through. Since we want to look at the manufacturer, we will add that field to our visualization pane under drill through:

Adding the manufacturer field from the data panel to the drill through section for the visualization panel.
Adding the drill through field

One thing you might not have noticed right away is the back arrow that was added to the page in the upper left hand corner. This allows you to return to the original page when you drill down on your data. By default, this is set to just go back to the last page. However, you might want to hard code a page navigation. This will ensure you return to the correct page and is a requirement if you allow drill through on multiple levels:

Screenshot of the back button that is added to the page along with the configuration of the button.
Configuring the back button added to the report canvas

You will note that with the drill through, there were two additional settings available to us. The first setting is on by default which is to keep all filters. This simply maintains the filtering from the page where the drill through occurred. So if it is filtered on a specific year, it will be maintained.

The cross-report drill through allows you to make this report page available on other reports. When published to the Power BI service, other reports in the workspace can drill through with this page and see the detail. This allows you to reuse this page on multiple reports which is very convenient.

Testing the Drill Through

With everything in place, it is time to test our drill through. Going back to the Sales Data page, we can right click on one of the bars in our quantity by revenue visual. From there, we can see the drill through menu option with our new report page:

Screenshot of the drill through menu on our quantity by manufacturer visualization.
Testing our drill through settings

If you have the ability to select your new page, then you did it right! Go ahead and click on it and you will see the new detail page filtered on the manufacturer:

Screenshot of the drill through report page filtered on a manufacturer.
Drill through report page filtered for Van Arsdel

It take a little work to configure, but the results are beautiful. It does a lot to help clean up your canvas and allow you to get more detail on a specific dimension. With a clean look like this, it makes it easier to consume your report with the ability to dig further for details.

Anything Else to Consider?

What if you want to use other fields for the drill through on the same report? You can easily facilitate that by adding them to the drill through field. This will give you the ability to use the same page for multiple dimensions to reduce effort.

In addition, consider the detail of your page when designing it. The ability to reuse it over and over again will reduce a lot of repeated effort. If you leverage the cross report feature, it could make your drill through page an extremely helpful feature to any report you create.

Conclusion

This is one of those unsung heroes of the Power BI Report. It is hidden in plain sight and not always easy to create. But with a little effort, it can really transform your report. It is worth the time as it will take the experience of your reports to the next level!

So, have you used drill throughs on your reports? What are your use cases? Did I miss anything? If so, tell me in the comments below!