Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
2 years ago
Solved

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      ...
Need Help
  • EaglesTony's avatar
    EaglesTony
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 output

 

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

  • EaglesTony's avatar
    EaglesTony
    Post Prodigy
    2 years ago

    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.