Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |