Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
mollycat
Frequent Visitor

Matrix with child values only showing parent-level attributes

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

IDDependency IDTitleContact
AETitle AContact A
B Title BContact B
CDTitle CContact C
CJTitle CContact C
D Title DContact D
ECTitle EContact 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 expandedTitleContact
ATitle AContact A
(A expanded to show E)Title A; Title EContact A; Contact E
BTitle BContact B
CTitle CContact C
(C expanded to show D)Title C; Title DContact C; Contact D
(C expanded to show J)Title C; Title JContact C; Contact D
DTitle DContact D
ETitle EContact E
(E expanded to show C)Title E; Title CContact 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...

IDDependency IDTitleContactDependency TitleDependency Contact
AETitle AContact ATitle EContact E
B Title BContact B  
CDTitle CContact CTitle DContact D
CJTitle CContact CTitle JContact J
D Title DContact D  
ECTitle EContact ETitle CContact C

 

 

Thank you!

 

1 ACCEPTED SOLUTION
xifeng_L
Super User
Super User

Hi @mollycat 

 

You can try below measure:

 

xifeng_L_0-1743144297544.png

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~

 

View solution in original post

4 REPLIES 4
xifeng_L
Super User
Super User

Hi @mollycat 

 

You can try below measure:

 

xifeng_L_0-1743144297544.png

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~

 

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 expandedMeasure - TitleMeasure - Contact
ATitle AContact A
(A expanded to show E)Title EContact E
ETitle EContact E
(E expanded to show C)Title CContact 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 expandedMeasure - TitleMeasure - Contact
ATitle AContact A
(A expanded to show E)Title EContact 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!

Hi @xifeng_L , do you have any suggestions for the issue I've described above? I have still not been able to find a resolution. Any guidance is appreciated!

Thank you!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.