Matrix with child values only showing parent-level attributes

[mollycat]

Hello, I have a data table with the following structure:

ID	Dependency ID	Title	Contact
A	E	Title A	Contact A
B		Title B	Contact B
C	D	Title C	Contact C
C	J	Title C	Contact C
D		Title D	Contact D
E	C	Title E	Contact E

An ID may have 0, 1, or several listed dependencies. My goal is to display the parent-level (ID) data in a matrix visual and then be able to drill down into the dependency values, showing the Title and Contact columns nested underneath. I've tried creating a hierarchy (matrix is still returning only the top level values, even when clicking into the dependencies; see table below) and using the PATH function (returning error that all IDs are not all included in the Dependency ID list of values).

Example of what is happening versus what I'm hoping for. After expanding, I'd like the Title and Contact to show the values associated with the dependency ID, rather than the parent-level ID (current value in black, expected value shown in red).

ID/Dependency ID after expanded	Title	Contact
A	Title A	Contact A
(A expanded to show E)	Title A; Title E	Contact A; Contact E
B	Title B	Contact B
C	Title C	Contact C
(C expanded to show D)	Title C; Title D	Contact C; Contact D
(C expanded to show J)	Title C; Title J	Contact C; Contact D
D	Title D	Contact D
E	Title E	Contact E
(E expanded to show C)	Title E; Title C	Contact E; Contact C

I also tried pivoting the dataset to have the dependent values brought onto the same line, but then am unsure that this is configured properly for use in a matrix...

ID	Dependency ID	Title	Contact	Dependency Title	Dependency Contact
A	E	Title A	Contact A	Title E	Contact E
B		Title B	Contact B
C	D	Title C	Contact C	Title D	Contact D
C	J	Title C	Contact C	Title J	Contact J
D		Title D	Contact D
E	C	Title E	Contact E	Title C	Contact C

Thank you!

[xifeng_L · Accepted solution]

Hi @mollycat 

You can try below measure:

Measure-Title = 
SWITCH(
    TRUE(),
    ISFILTERED('Table'[Dependency ID]) && MAX('Table'[Dependency ID])<>BLANK(),
        CALCULATE(
            MAX('Table'[Title]),
            TREATAS(VALUES('Table'[Dependency ID]),'Table'[ID]),
            ALL('Table'[Dependency ID])
        ),
    NOT ISFILTERED('Table'[Dependency ID]),
        MAX('Table'[Title])
)

Measure-Contact = 
SWITCH(
    TRUE(),
    ISFILTERED('Table'[Dependency ID]) && MAX('Table'[Dependency ID])<>BLANK(),
        CALCULATE(
            MAX('Table'[Contact]),
            TREATAS(VALUES('Table'[Dependency ID]),'Table'[ID]),
            ALL('Table'[Dependency ID])
        ),
    NOT ISFILTERED('Table'[Dependency ID]),
        MAX('Table'[Contact])
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[mollycat]

Hi @xifeng_L, I appreciate your original response and the Measures are working as expected until I apply a filter to the Matrix based on one of the Measures. Prior to filtering, the values are all showing exactly as expected (limited sample data here to highlight the issue):

ID/Dependency ID after expanded	Measure - Title	Measure - Contact
A	Title A	Contact A
(A expanded to show E)	Title E	Contact E
E	Title E	Contact E
(E expanded to show C)	Title C	Contact C

For example, I use the Measure-Contact to filter the Matrix where value = Contact E. After applying the filter on Measure-Contact, there are unintended issues where the filter is seemingly only applied to the Dependency values. The parent ID for A is retained with E listed underneath, but ID = E is no longer displayed.

ID/Dependency ID after expanded	Measure - Title	Measure - Contact
A	Title A	Contact A
(A expanded to show E)	Title E	Contact E

Additionally, when I try to filter where ID <> value, all parent ID's without children are removed, I'm assuming because of the way the filter is linked to the child value.

I've tried experimenting with all different combinations of KEEPFILTERS, REMOVEFILTERS, ALL, ALLSELECTED, etc. and am unable to come up with the right solution. Any guidance you can provide is greatly appreciated!

[mollycat]

Thank you!