Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Super Heroes,
Below is my sample data.
I need Output like below,
I created below measure, Which is working only when level 4 is there. If level 4 is not exist it will not showing my remaining values,
Below is my measure,
After using measure below is my output coming,
Kindly help please.
Hi @Anonymous ,
Here are the steps you can follow:
1. According to the selected column – Transform – Unpivot Columns.
Result:
2. Create measure.
Measure =
var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev3")),_a)
Measure 3 =
var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev1")),_a)
Measure 4 = var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev2")),_a)
Measure 5 = var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev4")),_a)
For reference =
calculate(MAX('Table'[Attribute]),FILTER('Table',NOT(ISBLANK([Value.1]))))
3. Result:
Please click here for the pbix file
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for help. I loose hope as no one is replying.
This solution has 2 major issues sir,
(1) Need to show in matrix so heirarchy is required.(ig level 4 iteam J is coming under what level of 1 ,2 and 3.
(2) If i applied this solution it increases row size very much.(if i have 500 rows as normal data set it will become 4500) So my actual data size is too much.
No solution?
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |