I need help with drilldown

[EaglesTony]

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

[EaglesTony · Accepted solution]

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]

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]

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.