Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am looking for some help in PowerBI.
We have 5 string measures called P1IncidentResolutionGrade, P2IncidentResolutionGrade, P3IncidentResolutionGrade, P4IncidentResolutionGrade, P5IncidentResolutionGrade
These 5 measures each can have values like 'Excellent', 'Good' , 'Fair' or 'Poor' depending on the incident resolution times
We have already computed these 5 measures.
Now we need to define another measure called OverallRating and these are the conditions for populating the value in it.
If all the 5 measures are 'Excellent' then OverallRating=Excellent
If any 4 measures are 'Good', then OverallRating=Good
If any 3 measures are 'Fair', then OverallRating=Fair
If any 1 measure is Poor, then OverallRating=Poor
Now how do we codify this in DAX? I thought of finding the count of measure for each set of values.
i.e for e.g. how many of the 5 measures are Good?
I was trying to compute the count of measures which are 'Good', in the below snippet
CountGoodPerformance =
var CountofGoodGrade = 0
IF ( [P1IncidentResolutionGrade] ='Good', CountofGoodGrade=CountofGoodGrade+1)
IF ( [P2IncidentResolutionGrade] ='Good', CountofGoodGrade=CountofGoodGrade+1)
IF ( [P3IncidentResolutionGrade] ='Good', CountofGoodGrade=CountofGoodGrade+1)
IF ( [P4IncidentResolutionGrade] ='Good', CountofGoodGrade=CountofGoodGrade+1)
IF ( [P5IncidentResolutionGrade] ='Good', CountofGoodGrade=CountofGoodGrade+1)
Return
CountofGoodGrade
This is not working.It is showing syntactic error.
How do i achieve this in PowerBI? Please help
thanks
Solved! Go to Solution.
Hi @msprog
Create measures
count of fail =
VAR p1_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Fair" ) )
VAR p2_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Fair" ) )
VAR p3_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Fair" ) )
VAR p4_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Fair" ) )
VAR p5_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Fair" ) )
RETURN
p1_f + p2_f + p3_f + p4_f + p5_f
count of good =
VAR p1_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Good" ) )
VAR p2_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Good" ) )
VAR p3_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Good" ) )
VAR p4_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Good" ) )
VAR p5_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Good" ) )
RETURN
p1_g + p2_g + p3_g + p4_g + p5_g
count of excellent =
VAR p1_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p1] = "Excellent" )
)
VAR p2_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p2] = "Excellent" )
)
VAR p3_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p3] = "Excellent" )
)
VAR p4_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p4] = "Excellent" )
)
VAR p5_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p5] = "Excellent" )
)
RETURN
p1_e + p2_e + p3_e + p4_e + p5_e
final measure =
IF (
[Measure p1] = "Poor"
|| [Measure p2] = "Poor"
|| [Measure p3] = "Poor"
|| [Measure p4] = "Poor"
|| [Measure p5] = "Poor",
"Poor",
IF (
[count of fail] >= 3,
"Fail",
IF (
[count of good] >= 4,
"Good",
IF ( [count of excellent] = 5, "Excellent" )
)
)
)
Hi @msprog
Create measures
count of fail =
VAR p1_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Fair" ) )
VAR p2_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Fair" ) )
VAR p3_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Fair" ) )
VAR p4_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Fair" ) )
VAR p5_f =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Fair" ) )
RETURN
p1_f + p2_f + p3_f + p4_f + p5_f
count of good =
VAR p1_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p1] = "Good" ) )
VAR p2_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p2] = "Good" ) )
VAR p3_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p3] = "Good" ) )
VAR p4_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p4] = "Good" ) )
VAR p5_g =
CALCULATE ( COUNT ( Sheet10[cate] ), FILTER ( Sheet10, [Measure p5] = "Good" ) )
RETURN
p1_g + p2_g + p3_g + p4_g + p5_g
count of excellent =
VAR p1_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p1] = "Excellent" )
)
VAR p2_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p2] = "Excellent" )
)
VAR p3_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p3] = "Excellent" )
)
VAR p4_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p4] = "Excellent" )
)
VAR p5_e =
CALCULATE (
COUNT ( Sheet10[cate] ),
FILTER ( Sheet10, [Measure p5] = "Excellent" )
)
RETURN
p1_e + p2_e + p3_e + p4_e + p5_e
final measure =
IF (
[Measure p1] = "Poor"
|| [Measure p2] = "Poor"
|| [Measure p3] = "Poor"
|| [Measure p4] = "Poor"
|| [Measure p5] = "Poor",
"Poor",
IF (
[count of fail] >= 3,
"Fail",
IF (
[count of good] >= 4,
"Good",
IF ( [count of excellent] = 5, "Excellent" )
)
)
)
Hi @msprog
Please see two links to the articles below, I think this should help you with this requirement.
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |