Forum Discussion

msprog's avatar
msprog
Advocate III
6 years ago
Solved

Help with DAX please

Hello, I am looking for some help in PowerBI. We have 5 string measures called P1IncidentResolutionGrade, P2IncidentResolutionGrade, P3IncidentResolutionGrade, P4IncidentResolutionGrade, P5Incident...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.