Help with DAX please

[msprog]

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

[v-juanli-msft · Accepted 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
Maggie

Community 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.

[Mariusz]

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.