Power BI Workaround – Drill Through Reporting in Power BI

So far I have seen many Power BI usage metrics demo’s or read blog posts about usage metrics, but not actually looked in detail. Recently I started exploring App Workspaces to create some standards for how to design, publish and collaborate with Power BI reports. In that process, I thought to look at Report Usage metrics by presuming I will be able to get Power BI Reports metadata. I don’t think it benefited for what I was looking for, but I saw Report GUID!  I remember seeing this before when I was working filters in Power BI Report URL.

By looking at the URL I thought, I could use the URL for Click Through and Drill Through.  So I started exploring to see if there is any possibility of Drill Through functionality. In a nutshell, Yes! You can just point to a report URL from Power BI Report and it will open that report when you click on it, like Click Through. If you also give filters like mentioned here – https://powerbi.microsoft.com/en-us/documentation/powerbi-service-url-filters/, it can work as Drill Through too. That gives you an idea, however, If you want to see what other things I explored or did with this then carry on reading…

Drill Through Reporting in Power BI:

By looking at my Report URL, I can see It formed with few Id’s, which looks like GroupID, ReportID and ReportSection. Getting GroupID and ReportID is doable but what is this ReportSection? Report Section, it does have a pattern, the very first tab is Report Section, the second one is Repost Section 1 and goes on. Oh Yeah, I also understood that I better call each tab as Report Sections than tabs. (Group ID are Office 365 Group Id’s, and Report ID’s are available through Power BI Usage Metrics)

Even if you understand Report section was based on the position, it could be a bit tedious process to figure out the position of Report Section. It is not actually the position; it’s based on which section created when. At this point, I could easily guess, this info hidden somewhere in Power BI files.

Report Section:

One of my good friend Malden, who is also very talented, once showed me how easily he was getting content from Excel by renaming as zip, he also mentioned it works with many Microsoft applications. So I thought to rename Power BI file to Zip file. For my convince after unzipping, there was a welcoming folder called Report.

Inside Report folder, there were two files

By opening these files in Notepad, I noticed Layout was a Json file type, and  LinguisticSchema was XML. Also, these two files have some information about Reports.

One useful tool I knew which could pull data from these file types is Power BI. Naturally, I renamed files with respective file types and started importing into Power BI.

For both of them, I got an error. I thought something wrong with the file format. Then I checked both files with XML Format Checker and Jason Format checker. Both of them were fine. However, when I copied content from those files and saved as new copy using Notepad, they were ok to import into Power BI desktop. (No idea why. I didn’t want to dig deeper, I must have done something wrong).

So, once I imported data into Power BI, I found a new terminology called POD. Each Report section was called like PODx. It might be some programming terminology, which I don’t know. Whatever, after bit of clean up this is how data in my queries look like

I can join these two queries to get which Report belongs to what section.

At this stage, I can easily create URL using GroupID, ReportID and RepostSection. To make it bit more interesting (at least for me ), we can use query parameters to get ReportID and GroupID.

Using query parameters to create Drill Through URL:

I created two parameters one for GroupID and one for ReportID. Then I created two queries by returning those parameter values so that I can use them for my Drill Through URL and edit parameters from the designer.

Then I created a calculated column under Layout Query (Query with Report Section information)

ReportURL = "https://app.powerbi.com/groups/"&MAX(GroupID[GroupID])&"/reports/"&MAX(ReportID[ReportID])&"/"&'Layout'[ReportSection]

Another calculated column as follows to form a full URL with filters

RegionURL =

Var RegURL = CALCULATE(MAX(Layout[ReportURL]),Layout[Report Name]="Region")

return

SUBSTITUTE(RegURL&"?filter=Schools/Region eq '"& CALCULATE(MAX(Schools[Region]))&"'"," ","%20")

Now I have a drill through URL for each Region.

And, I can create a report like below to drill through on each Region

And it works 🙂

But what if you want to drill through to an app?

Drill through to a published App:

APP Workspaces URL – https://app.powerbi.com/groups/me/apps/8a9deb71-100d-4c43-9585-5d7738d92caa/reports/18728640-81c2-4d24-8dbf-436a51d5416a/ReportSection

In the above URL, we have AppID and ReportID; ReportID is same as App Workspaces. Just instead of GroupID, we need to use AppID and small changes in the URL. I didn’t manage to find a way to query AppID other than copying from URL.

So to the PowerBI Model, we can add another Query Parameter saying APPID, and change Drill Through URL based on the sharing.I don’t want to make this post any longer If you really want, I am sure you can write that DAX expression.

Summary:

Now the basic question, does anyone want to do it? May be No! One benefit I can see is, when Microsoft bring Drill through functionality into Power BI, it could be limited to work with in a model or with in a Workspace. But this way we can point to any URL in any workspace. Another benefit for me, it is nice to know how Power BI Service URL was structured and few other things like Report folder in Power BI file and knowing Report Section terminology 🙂

In summary, if you have a burning Drill through the problem, then this could be handy. Few quick points, works only in Power Bi Service. Filters does not work with Public publish. There is no Back button like we expect with any drill through functionality. You can point to any report in any workspace. When you click on the URL, it opens a new window/tab for each URL. It was slow for me but it could be just my internet connection. If you have more than one report, then you can use Usage Statistics and get ReportID for all reports. I am sure there must a way to get Office 365 group ID’s using Power Shell.

To be frank, very soon Microsoft is going to release Drill through functionality, so there is no point of going through all this pain other than if you love looking into Power BI files 🙂

Prathy 🙂