Help with Drill Down or Drillthrough Functionality

[Feesern]

Hello,

I've started using Power BI just a few days ago and I have the below table being generated. From this table, I would like the ability to drill down to see what makes up each one of the dollar amounts. For example, I need to be able to click on the Orig. Budget, Adjustment, Variances, Incurred, On Order amounts for the ms150 project and see what values make up all of those values.

What is the proper way to make this happen? I've tried turning the data points into hyperlinks that will link to another page in the report and filter using URL parameters. This didn't work for me because if I try to create a custom column with a hyperlink it removes the data value. I would like the data value to remain the same as it currently shows in the table, but turn it into a link that I can send the user to another page and pass through a few variables (based on the project, building, and unit of the row they clicked on). I don't believe this is possible in Power BI.

I also tried building a hierarchical drillthrough, but this doesn't seem to have the proper results I'm looking for. I wasn't able to drill down on ANY of the data points in the table.

I also tried adding the required filters to separate pages on the report and linking them all. So the user would select a project and building on the table above, then go to a "Variances" page that shows the details based on the project and building selected on the main page. I got stuck here because the 'drill-down' data comes from many different tables (all joining based on the project, building, unit) and I couldn't find a way to make the selected filters from the first page apply to filters from different database tables.

Any help is greatly appreciated. I truly wish I could get the first option working where you click the data point and you're taken to a page that shows the details of what makes up the number you clicked on.

[dedelman_clng · Accepted solution]

In my experience, drillthrough is probably going to be your best bet, where you have it "linked" to a different page that contains the details (others may know differently).  PowerBI adds an arrow at the top left of the destination page to send the user "back" to the original page.

On the destination page you put the key value under the "Drillthrough" section under Filters. In your case you might have to calculate a composite key (project-building) to use as the key value. If you are modelled correctly the drillthrough filter should work the same way as an explicit filter on the detail page. 

However, note that you may need as many detail pages as you have "drillable" columns (5 in your case?) unless you can rig up measures and slicers that switch between the various spend types.

I know this likely wasn't what you wanted to hear, but hope it helps. Others may have different creative solutions.

David

[Feesern]

Thanks for the quick reply, @dedelman_clng. I think what you've recommended would work for us if I can get it working. So are these my next logical steps:

Create a new "composite key" column consisting of the project-building-unit

Create a matrix (since tables don't work with drillthrough, is this correct?) which uses the composite key above

Create a detail page that also uses the composite key above

Does this sound right? Also, what if my drillthrough detail data exists on different tables? I can create the composite key for both the high level and detail queries, but will I be able to navigate to the detail view like this? I don't think I can merge the queries for different tables as it causes lots of duplication and sums to be inaccurate.

Again, thanks for your help, it is appreciated.

[dedelman_clng]

You can use drillthrough on a matrix or a table.  The "Drillthrough" option when you right-click on the "parent" table/matrix will appear once there is a "child" table/matrix that has the same key.  Your first 3 steps look correct.

As for the measures, it is all going to depend on how the data is modeled and how the measures are calculated.  Properly modeled and with the proper DAX, the drillthrough filter should work just like any other slicer - applying filtering to every measure and recalculating as such.

If you need help calculating various measure that react correctly, post them either in this thread or as separate questions.  Always try to include a basic model (showing relationship between tables) and at least some sample data with required outcome(s) based on that data.  Also putting a stripped down version (removing sensitive data) of your PBIX on a DropBox/OneDrive/GoogleDrive helps the community troubleshoot.

Good luck!

David

[Feesern]

Ok, I've made it a bit further. I completed the steps mentioned above. I created a composite key of project, building, and unit. I added this to the "parent" table:

I also created the same on one of the tables I want to drill to and added it to the view:

I created a relationship between the two tables using the composite key:

I added the composite key to the Drillthrough filters section on the "child" page:

But when I right-click on the "parent" report, I don't seem to get the drillthrough menu. I'm sure I'm doing something silly. If you have any insight, it would be much appreciated.

Thanks.

[dedelman_clng]

Everything sounds correct so far, so here's a couple of things to check:

First, make sure you have a relatively up-to-date version of PowerBI.  Always best to be on one of the latest 2 or 3 versions.

Second, make sure that the field you put in the Drillthrough Filter box is from the *parent* not the child table (I'd wager it's going to be this one).

If that still doesn't work, see what happens when you make the filter "bi-directional" between the child and parent tables in the model.

Finally if it's still failing, is there any way you can share the PBIX with cleansed data? Any of the common file sharing sites are generally used by people to share their files.

Hope this helps

David

[Feesern]

You were right. It ended up being the drillthrough I was using was the one from the child, not the parent.

I truly appreciate your help.