Power BI – Cross-Report Drillthrough From One Report To Another Report For Increased Security

POWER BI – DRILLTHROUGH FROM ONE REPORT TO ANOTHER REPORT FOR INCREASED SECURITY (CROSS-REPORT DRILL THROUGH)

APPLIES TO: ✓ Power BI Desktop ✓ Power BI service

The traditional drill-through does not allow to drill through from one report to another report. It only works in a single report. It is a page-to-page drill-through.

Cross-report drill through allows you to drill through from one report to another report.

At first, I didn’t think this was going to be that great of a feature but then I realized this serves a big purpose. It’ll allow you to kind of set up a little bit more security around your reporting.

For example: Let’s say as a Manager, I want to be able to see detailed data but I don’t want my employees to see this kind of level of detail. I only want them to see the summary report. We can now set up a drill through from the summary to the details report but not give them access to the detail report. So, it’s kind of serving that purpose of keeping things secure but not having to create multiple reports for your different users that have different access. This is different than row-level security because row-level security is going to allow you to restrict the actual data that’s shown to them not the actual report that’s shown. So, we’re going to restrict what they can see based on a report level.

CROSS-REPORT DRILL THROUGH

I have one page in one report let’s call it my source report. A source report is a report that will use the visual that you’ll use to invoke the drill to the other report. The other report that you land on we’re going to refer to that as our target. That’s going to be the page that you land on after the drill is initiated from your source report. So, in two separate reports, I’m going to go from one page of my source report to another page in the target report.

Scenario: I have one report that contains everything I need from a pretty high level and I saw another report that contains some other information I need. So, I’m going to show you how can I link them up. All of this is possible using the Cross Report Drill Through.

Step 1. Open your TARGET REPORT, on your target report open up the VISUALIZATIONS PANE and you’ll see a little toggle for CROSS REPORT, switch it to ON.

Unleash the Potential of Power Platform With a Center of Excellence

Business innovation often comes from within. Discover how to empower innovation from non-traditional developers with the Microsoft Power Platform.

Learn More

The next thing you have to do is you have to identify the column that you’re going to use to connect those two reports. One of the main requirements is that you need to have those columns and they need to be named the same and they need to exist in both reports but in addition to that, the table names the tables that contain those columns those names must be the same also. The table and the column names must be exactly the same across both of those reports.

Step 2. After you identify that column you just drag it down to the drill-through section.

Step 3. Then all you need to do is go to FILE → OPTIONS AND SETTINGS → OPTIONS and then what you going to see there. There are two sections GLOBAL and CURRENT FILE in CURRENT FILE look for REPORT SETTINGS and you’re going to see a property called CROSS REPORT DRILL THROUGH and there’s going to be a property ALLOW VISUALS IN THIS REPORT TO USE DRILLTHROUGH TARGETS FROM OTHER REPORTS (✓Check it) you have to click that and then click OK.

Note: Do this process for both the reports (Source & Target)

Save the report and then publish it. If you forget to do this on the Power BI desktop that’s okay, I’m going to show you to do it in the service also.

Go to your workspace where you published these reports if you forgot to set that property on your Power BI desktop. Once you go to your report network space, you’ll see a little gear on either one of those reports. You need to remember this property to be set for both the target and the source center target however you want to line them up.

You click on that gear (SETTINGS) scroll to the bottom if it doesn’t appear, and you’ll see CROSS REPORT DRILL THROUGH make sure it’s gold (TOGGLED ON) and repeat that for both reports.

Just need to make sure that the table and column from your target report exist in the source report and ensure that you can get the context of that value when you pass it in other words. I have a table that contains the order number so when I right-click, it’s going to grab that order number pass it to the other report, and filter that page by that order number.

Step 4. Go to your SOURCE REPORT. What you want to do is finally know the order that you want to drill through RIGHT CLICK it → SELECT DRILLTHROUGH and what you going to see is the name of the page with the actual report name in brackets. So, I’m going to go from my source page over to THE CUSTOMER DETAILS PAGE IN THE TARGET REPORT.

Step 5. Now what’s going to happen is that it takes that order number and passes it to another report in the same workspace with keeping all filters and also filtering the report for that order number that you select while initiating the drill through.

Important points to note:

• Both these report sources and targets must be in the same workspace.
• The table and the column names must be exactly the same across both of those reports.
• If you set Keep all filters to ON when you set up the target page, filter context from the source visual can include the following:
  • • Report, page, and visual level filters that affect the source visual.
    • Cross-filter and cross-highlighting affect the source visual.
    • Slicers and sync-slicers on the page.
    • URL parameters.