Forum Discussion
Help with Drill Down or Drillthrough Functionality
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
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.
Community ChampionYou 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
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
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.