Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have a long table that contains some dimensions and a value associated to them.
These dimensions have an incomplete hierarchy relation amognst them. In a simple chart I would like to display the value associated with the lowest selected hierarchy level. I include an example with only 1 dimension below (my real cas has 5).
Hierarchy table:
LEVEL 1 | LEVEL2 | LEVEL 3 |
Food | Fruits | Bananas |
Food | Fruits | Apples |
Food | Meat | Chicken |
Food | Mushrooms | Mushrooms |
DIMENSION | VALUE |
Food | 10000 |
Fruits | 2000 |
Meat | 3000 |
Bananas | 500 |
Apples | 500 |
Chicken | 1500 |
Mushrooms | 100 |
My expectation is to have a drilldown that at level 1 shows:
X | Y |
Food | 10000 |
At Level 2
X | Y |
Fruits | 2000 |
Meat | 3000 |
Mushrooms | 100 |
At Level 3
X | Y |
Bananas | 500 |
Apples | 500 |
Chicken | 1500 |
Mushrooms | 100 |
I believe this logic can be built using RELATED() but I haven't found a way to do so. What is the best way to create such logic in DAX? Is there an approach to embed that in the m power query load side?
Thanks in advance
Solved! Go to Solution.
Hi @Anonymous,
Please refer to below measure:
return value = VAR isLevel1 = ISINSCOPE ( 'Hierarchy Table'[LEVEL 1] ) VAR isLevel2 = ISINSCOPE ( 'Hierarchy Table'[LEVEL2] ) VAR isLevel3 = ISINSCOPE ( 'Hierarchy Table'[LEVEL 3] ) RETURN IF ( isLevel3 = TRUE (), CALCULATE ( SUM ( 'Dimention Table'[VALUE] ), FILTER ( ALLSELECTED ( 'Dimention Table' ), 'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL 3] ) ) ), IF ( isLevel2 = TRUE (), CALCULATE ( SUM ( 'Dimention Table'[VALUE] ), FILTER ( ALLSELECTED ( 'Dimention Table' ), 'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL2] ) ) ), IF ( isLevel1 = TRUE (), CALCULATE ( SUM ( 'Dimention Table'[VALUE] ), FILTER ( ALLSELECTED ( 'Dimention Table' ), 'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL 1] ) ) ) ) ) )
Best regards,
Yuliana Gu
Hi @Anonymous,
Please refer to below measure:
return value = VAR isLevel1 = ISINSCOPE ( 'Hierarchy Table'[LEVEL 1] ) VAR isLevel2 = ISINSCOPE ( 'Hierarchy Table'[LEVEL2] ) VAR isLevel3 = ISINSCOPE ( 'Hierarchy Table'[LEVEL 3] ) RETURN IF ( isLevel3 = TRUE (), CALCULATE ( SUM ( 'Dimention Table'[VALUE] ), FILTER ( ALLSELECTED ( 'Dimention Table' ), 'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL 3] ) ) ), IF ( isLevel2 = TRUE (), CALCULATE ( SUM ( 'Dimention Table'[VALUE] ), FILTER ( ALLSELECTED ( 'Dimention Table' ), 'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL2] ) ) ), IF ( isLevel1 = TRUE (), CALCULATE ( SUM ( 'Dimention Table'[VALUE] ), FILTER ( ALLSELECTED ( 'Dimention Table' ), 'Dimention Table'[DIMENSION] = SELECTEDVALUE ( 'Hierarchy Table'[LEVEL 1] ) ) ) ) ) )
Best regards,
Yuliana Gu
Hello @v-yulgu-msft,
Thanks a lot, your approach looks close to what I'm looking for.
Is there a more generic way to build this?
I will likely use more than 1 'Hierarchy Table' and perhaps I play around a bit with the number of levels.
Should I make 1 measure per 'Hierarchy Table' and 1 var per level in each hierarchy.
Hi @Anonymous,
I will likely use more than 1 'Hierarchy Table' and perhaps I play around a bit with the number of levels.
Should I make 1 measure per 'Hierarchy Table' and 1 var per level in each hierarchy.
Yes. You should create one measure per 'Hierarchy Table' and define one variable per level in each hierarchy.
Best regards,
Yuliana Gu
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 |
---|---|
76 | |
76 | |
56 | |
38 | |
34 |
User | Count |
---|---|
99 | |
56 | |
51 | |
44 | |
40 |