Forum Discussion
Help with DAX please
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
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" ) ) ) )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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/
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the 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" ) ) ) )Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
