Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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!
Solved! Go to 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~
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~
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!
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |