Forum Discussion
I need help with drilldown
I have a single table that has all my data broken down as follows:
Key Type Name ParentKey
1 Initiative SomeName
2 Initiative SomeName2
3 Epic SomeName3 1
4 Epic SomeName4 2
5 ChildEpic SomeName5 4
I have a main report with just Initiatives
The 2nd report is fileterd by Epic and has the Drill through field as ParentKey
This is working, in that when i right click on Initiative #2 it shows me Epic #4
The 3rd report I have having an issue, what I want is if I right click on Epic #4, I want to show ChildEpic #5, but when I put the Drill through field as Parent Key on this, is is not letting me do right click to drill through on the 2nd report ?
I was able to create a column in the table and the table looks like this now:
Key Type Name Parent
1 Initiative SomeName 1
2 Initiative SomeName2 2
3 Epic SomeName3 2
4 Epic SomeName4 1
5 Epic SomeName5 1
5 ChildEpic SomeName6
It seems I can go down 1 level, but not a 2nd level ????
I need to have a drill down from Initiative to Epic, so when I right click and drill down on Key 1 (Initiative), it will
show:
SomeName4
SomeName5
If needed, I could break up into multiple tables, if that is a better design
I was able to figure it out.
I had to do some merge of individual tables via left joins until the final table had all the data for Initiative, Epic, Child Epic, so there had to be a flattened table in order to then be able to use different fields in this table in the drill through.
3 Replies
Hi EaglesTony ,
Based on your description, you want to implement a secondary drilldown to get the page results of each drilldown. If you want to drill down across reports, you can only drill down on the only data that matches the results. However, you can use a synchronized slicer in conjunction with measure to achieve this functionality.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:Create a new table
Table 2 = VALUES('Table'[Key])Use table as the slicer of three pages an create measures
Measure = IF( SELECTEDVALUE('Table'[ParentKey]) = SELECTEDVALUE('Table 2'[Key]), 1, 0 )Measure 2 = VAR _vtable = FILTER( SELECTCOLUMNS( ALLSELECTED('Table'), 'Table'[Key], 'Table'[ParentKey]), [ParentKey]=SELECTEDVALUE('Table 2'[Key])) RETURN SUMX(_vtable,[Key])Measure 3 = IF( SELECTEDVALUE('Table'[ParentKey]) = [Measure 2] && SELECTEDVALUE('Table'[ParentKey]) <> BLANK(), 1, 0 )Apply [Measure] to the table filter of the page 2 and [Measure 3] to the table filter of the page 2
Hide the slicer of other two pages
Final outputBest regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Is there an easier solution ?
I was hoping for some sort of 2nd table and use a field from that as the drilldown field.
I'm open to using 3 tables (Parent/Child Relationships)...I was thinking
Initiative Table
Epic Table (child table of Initiative Table)
Child Epic Table (child table of Epic Table)
So the logic would be
Initiative--->Drill Down to--->Epic---Drill Down to---Child Epic
Thus, I want to drill down a total of 2 levels.
I was able to figure it out.
I had to do some merge of individual tables via left joins until the final table had all the data for Initiative, Epic, Child Epic, so there had to be a flattened table in order to then be able to use different fields in this table in the drill through.
