It is very common to have multiple reports for different audiences, while there is also one (group of) user(s) that requires to have an overview over all these different insights. The main challenge you will face, is having cross-report interactivity and find related insights.

Let’s take an example of three different roles, where we have a customer account manager, reseller manager and a regional manager. Of course, they should have the same single source of truth, but there is one thing you want to avoid as report creator! You do not want to create three different report for the three mentioned audiences. But as they have different roles and responsibilities, you do not want them to see each other’s data and keep it clean and simple! In this blog I will describe how you can setup cross-report drill through to jump from one report to another, while respecting applied filters and avoiding building three separate reports!

The case

To start with, I will elaborate on the case that was briefly explained above. Imagine you run a multi-national sales organization (Adventure Works used in the samples). That will bring a organization structure with multiple people in different roles and responsibilities. To explain the cross-report drill through functionality, I distinguish the following three roles.

• Customer sales manager, who is responsible for all business to consumer sales (B2C) and wants to have an overview of sales by region, by year, and deeper insights in the customers that buy at his stores.
• Reseller manager, who requires insights over all resellers he/she works with, which is typically a business to business (B2B) scenario. The reseller manager is not necessarily interested in the B2C customers of each store specifically and wants to have the insights presented on a slightly higher level.
• Regional manager, who focusses on different markets and segments and not in specific B2C or B2B but is responsible for the company performance in a country or region.

Typically, this will result in three different reports. Though, if there are things that need attention, the regional manager potentially wants to jump from one report to another to find cross-market (B2C / B2B) related insights. As an example, I have setup the reports for the Customer Sales manager and for the Reseller manager.

Considered solutions

There are multiple options how this challenge can be solved nowadays. Below I summarize a few solutions that can be considered.

• Direct Query for Power BI datasets and Analysis Services (also known as Composite Models) which allows you to combine multiple tabular models, either from Power BI or Analysis Services into one new model. This can potentially help to combine the B2B and B2C specific data models in one new model as a third data model for the regional manager.
• Object Level Security (OLS) in Power BI which can secure tables that are not meant for a specific audience, while having one big data model to cover it all. With this you can build one data model to rule them all and securing the sections that are not intended for a specific audience and build separate reports to look at the model from all different angles (B2B / B2C / Regional). Note: OLS requires Power BI Premium.
• Query string parameters in URLs to apply filters on a Power BI report by jumping from one report to another and passing the filter in the URL. With this, you will still have separate data models for B2B and B2C, but you allow your regional manager to jump from one report to another, while passing the filter along to the other report.

The first two options are data modeling related topics and will result in a lot of complexity. Both require additional development and maintenance work to be done and not directly relate in cross-report interactivity to jump from one report to another.

The query string parameters in the URL seems to be a good option. Though this requires a hard-coded part to paste the report URL and configure as a button on the report to navigate from one to another. As the URL contains the workspace GUID and report GUID, this requires updating the URL when you deploy from a test workspace to production, or when you delete and re-publish the report. Both will result in a new updated GUID that results in a error sensitive solution.

In the remaining of this blog, I will explain how cross-report drill through functionality solves this challenge.

Cross-report drill through

Drill through, is a functionality in Power BI that allows you to take the current data point and apply this as a filter at another page. This can be done via a right-click on a visual, or via a button as I described in an earlier blog about discoverability of drill through.

With cross-report drill through, you will be able to jump from one report to another while taking the filter with you, just like you do with normal drill through. A very powerful feature in the visual aspects of Power BI! To make it work, there are a few things that we must take into account.

• Cross-report drill through cannot jump from one workspace to another. The reports must reside in the same workspace (or app).
• The reports need to share the same table and column names, for the one that is used as drill through filter. Power BI uses the name of both the table and column to apply the filter. Typically this is a dimensional table, as you mostly filter the fact table based on the dimensions in a star schema.
• The cross-report drill through needs to be specifically enabled in the report settings (either in Power BI desktop or directly in the Power BI service).
• Toggle-on cross-report in the drill through pane while editing the report.

After you have applied above steps, you will be ready to go! Please note that this can only be tested in the Power BI Service, as Power BI Desktop does not know in which workspace the report lives and only renders one report at a time.

Following the before described example, the regional manager wants to have insights in both the Customer sales as the Reseller sales reports. Therefore he/she wants to jump from one report to another. In below GIF shows how the right-click on country allows the Regional manager to jump to the other report, while passing the country as a filter to the other report. Cross-report drill through can be recognized by the name that shows up in the right-click menu, where the name of the report is included at the end between square brackets.

Tips & tricks

Finally, there are some tips and tricks I want to share to take optimal benefit from this functionality.

Back button
Let’s start with an easy one. As soon as you drag-and-drop a field in the drill through section in Power BI Desktop, this will automatically generate a back button on your canvas. This button is intended to navigate back to the page where you came from when you navigate within the same report. With cross-report drill through, this does not work unfortunately. As you jump to another report, Power BI does not know how to navigate back to the previous page, as this resides in a different report. Therefore, consider removing the back button from the canvas.

Keep all filters
One of the options to enable with drill through, is to keep all filters. The keep all filters toggle, will push all currently applied filters as drill through filters. But as there is no requirement that the data models are exactly identical, this might also result in weird results. Imagine that in above examples I pass along a filter on the customer table to the reseller report. As a reseller manager, I might get confused if I see a customer filter showing up in the right-hand filter pane. My advise would be to consider disabling the keep all filters option.

Persistent filters
Within Power BI, filters will be saved automatically. This intended behavior results in the fact that a user opens a report with the same filters applied as when he/she left the report for the last time. Normally this is not a big deal, but with cross-report drill through, there might be filters applied that confuse the user next time, as he/she is not aware of the drill through context that was applied last time. To avoid these confusing situations, either create a reset all filters button, or disable the persistent filters in the report settings. This can be done in Power BI desktop in the report settings: File > Options & Settings > Options > Report Settings > Persistent Filters. By enabling this, you do not allow users to save filters to the report in the Power BI service.

Central dimension in dataflows
Use dataflows to strive for a single source of truth and make sure you have identical table and column names in all data models that you want to use with cross-report drill through. By saving the most common tables in dataflows in a central place, you can benefit from some sort of basic master data management. In the example situation I described above, I identified a few tables that are shared across both data models. Those tables are defined in the dataflows and used in both data models as shown in below lineage overview.

Wrap up & remarks

Finally, lets wrap the solution described in this blog. I believe that cross-report drill through can open-up new scenarios to find cross-report insights and bring different use cases together in centralized solutions. As described above, there are a few things that you must take into account, such as the reports living in the same workspace. This can be a blocker for sharing the content some scenarios, but I believe this can be overcome by applying decent security on the dataset level with row level security and/or object level security.

As cross-report drill through exists in Power BI for around two years, but I have not heard my colleagues nor clients about using this functionality in their solutions, I think that cross-report drill through is not often used. I have to say, this is not based on any analysis but just on my gut feeling. With that, it is a very well hidden strength of Power BI.

A year ago, the regular drill through made a huge step forward in discoverability, by triggering the feature via a button on the report canvas, I believe there is still a step to take for cross-report drill through. As cross-report drill through only shows up with a right-click on a visual, this might be a blocker for adopting this functionality.

While writing this post, I became enthusiastic about this functionality and already have solutions in mind at the client side where cross-report drill through might open new possibilities. Especially when you have multiple reports on top of the same datasets, this should be a quick win to start implementing as of today!

Do you want to try this out yourself? Below I also shared the link to the Adventure Works DW 2020 SQL Server backup file and pbix file on GitHub to easily setup a demo case yourself.

Links

• Microsoft Documentation: Direct Query for Power BI datasets and Analysis Services
• Microsoft Documentation: Object Level Security
• Microsoft Documentation: Query String parameters in URLs
• Microsoft Documentation: Cross-report drill through
• Blog: Power BI drill through, powerful but hard to find, until now!
• Demo dataset: Adventure Works 2020 bak file and pbix file