Power BI Drillthrough is a powerful feature that enables users to quickly and easily explore the data that they are most interested in. Developers can use this feature to let users navigate between different levels of data without leaving the report that they’re currently on.
We’ll explore how Power BI developers can create dashboards with this extremely useful feature.
Setup a Power BI drill through report by creating two tabs in the same report. One with a summary visual, the other with a detail visual. Drag a field common between the two visuals to the “Add drill-through fields here” option on the visualization panel of the detail tab.
Once a drill through report is setup, a user will be able to right click on a summary visual and select drill-through. Power BI will automatically detect the connection and navigate the user to the new detail tab.
Power BI gives developers a lot of granularity when setting up drill through reports.
It is important to have proper relationships established within the Power BI data model for this function to work.
Advanced features of drill through include adding custom navigation buttons to easily go back to the original page of the report and the ability to navigate between completely different reports within the same Power BI workspace.
What is a Power BI Drill Through Report?
Before diving into the buttons and technical setup, it’s good to have a brief overview of the pieces of the Power BI report that we need to setup.
Drillthrough works as a navigational technique that allows you to create a more detailed report page focused on a specific entity in your dataset.
A high level overview of setting up drill through will involve the following steps.
- Setup: In your Power BI report, you create a new drill through page that provides greater detail than a summary report. This is the tab that will be linked to later. A Power BI developer creates a drillthrough filter based on a specific field from your dataset.
- Linking: Once the drillthrough filter is establish, the page becomes linked with the other tabs of the Power BI report based on the fields used as a drillthrough filter.
- Interaction: After the report pages are linked, you can right-click on a data point in a chart or table and select the drillthrough option.
- Detailed View: Power BI will take you to the drillthrough page which is specifically designed to provide more detailed information about the category selected on the previous visual.
The Drillthrough function lets you create a bridge from a general overview to a more focused view. This helps users or business managers to get more detail that’s already been formatted and curated by the Power BI Developer.
How to Setup a Power BI Drill-through report
Drillthrough reports sound complicated, but in practice they are really easy to setup!
In our example, we’ll assume that people have a basic understanding of setting up table or chart visuals. This technique works on both and is not limited to a single type of visualization.
In our drill through example, we will setup two visuals.
Visual #1 – A Sales Summary bar chart of total sales by date.
Visual #2 – A pie chart of Sales Dollars by Item Sold.
Each one of these visuals is setup on its own tab within the same Power BI report.
The Sales Summary tab and visual will be our starting point.
It uses Date on the X-axis and Sales on the Y-axis.
Once you create a second detail visual drag a common field to the Drill Through section.
After adding the field to the Drill Through section, you can go back to your summary page and right click the summary visual.
A new option is available called Drill Through. The page that’s available is the detail tab that we just created and linked through the date field.
By linking the two report pages together, users will be able to drill from seeing summarized sales by date to a detailed view of sales for that period broken down by item.
Modifying The Power BI Drill Through Back Button
An easy way to determine if a Microsoft Power BI drillthrough report was correctly setup, is that you will see a back button in the top left of the page that has been identified as a details page.
We recommend making the back button larger than default or customizing the icon
Unless report viewers are already familiar with Power BI, the back icon can be confusing or hard for them to see. It’s a very common point of confusion.
You can click on the icon itself to adjust the size.
To create a custom Back Button in Power BI Following these steps:
- Create a new button, shape, or Import an image – These options are available under Insert section of the Power BI Ribbon. Power BI has a number of pre-made shapes and buttons for different styles of arrows and common navigation tasks. If you ever delete the default graphic, it’s available to replace under the Buttons menu.
- Define the Action Upon Click – Select the image, button or shape that you want readers to interact with. Go to the Shape section of the Format Panel. At the bottom of it there is an Action section that will allow you to define the action to take. Select Back and it will navigate people to the previous tab of the Power BI report.
Additional Considerations when Creating Drill Through Reports
As you deploy drill through functionality in your Power BI reports there are a number of things that are good to know.
A relationship must exist between drill through fields
All data is on a single table, but data can be on multiple tables as long as there is a relationship built between them in the Power BI relationship manager. Alternatively, you could join or append multiple tables together using the Power Query editor.
Hide the detail tab for a cleaner report
You can right click on any tab within a Power BI report and select “Hide Page” This will prevent the tab from being visible to the end user once the report is published to PowerBI.com. Developers will still see the tab, but it will have a small hidden icon next to it.
By hiding detail tabs, users will have to drill through a summary report to get to it. Otherwise it will show up on the list of available dashboard pages.
You can Designate Visuals as Drillthrough Visuals
When you select a visual on the dashboard canvas, you will find a Data/Drill section of the Power BI ribbon. It contains a Drillthrough button.
This button designates a visual as a drill through visual.
The button modifies the behavior of a visual.
Normally a user will click on a column of a chart and it will highlight the specific column and filter the other visuals on the page.
When Drillthrough is enabled for the visual, a left click will bring up a contextual menu to drill through to another report, like in the example below.
Keep All Drillthrough Filters Option
Developers have a choice when building reports. If you enable the Keep All Filters option on the summary visual any slicer selected on the original page will follow through to the detail page.
Sometimes this is useful if you have a highly dynamic report where people are filtering with a number of slicers for a very specific view. If you enable the feature those same filtering criteria will follow through to the detail page.
We recommend testing this feature as detail pages will not always make sense with the same filters enabled.
You Can Have Multiple Drill Through Detail Pages
Users can have multiple drill through pages. This is especially useful for times when business users will want to drill into data based on different criteria.
The process works the same as the initial example, and as long as there is a drill through relationship established, you will be able to choose which tab you want to use.
In the screenshot below, we created a new Sales by Quantity tab and hid both of the tabs so the end user would be unaware.
Setting Up Power BI Cross Report Drillthrough
Cross Report drillthrough is another very powerful feature of Power BI. It is less commonly used, and takes a few extra steps to setup.
Cross Report drillthrough allows developers to link different Power BI reports that have been published. Meaning that users can seamlessly switch between pages of different reports. Each report is contained in a separate .pbix file.
To setup Cross Report Drillthrough follow these steps:
Enable Cross-report Drillthrough and define a common column.
This is the same step as above, with the added step of toggling cross-report drill through on.
Note: For this to work, the table and column names must be the same between the two different Power BI files.
Enable Cross-report drillthrough at the Current File Level.
Navigate to File > Options > Options & Settings.
Then select Report Settings and enable Cross-report drillthrough. By default the option is disabled, and will have to be turned on for each Power BI report that you want to allow as a drillthrough target.
Note: You will need to enable this option for both the source and target reports.
Once enabled, you will need publish both reports to the PowerBI service. There is no way to link between locally saved .pbix files.
Once both reports are published to powerbi.com, you can view the report settings and ensure that drill-through is an enabled option for both.
There are quite a few steps to enable cross-report drillthrough across multiple Power BI reports.
Microsoft Guys in a Cube have done a great summary to visualize each step of the process.
Conclusion
Creating Microsoft Power BI drill through reports is an incredibly useful feature. When readers right-click on a visual they can be brought to a page that has more detail. By enabling the drill through toggle, viewers will be prompted to drill through instead of focusing on a filter.
Filters can be setup to carry through to the new detail page, or you can choose to present viewers with a completely different view of their data in the detail destination as long as there is a commonality established within the data model.