If Then or Switch Or Power Querry

[cflynn_29]

Hi everyone,

There are a few ways to do it but i was hoping to do it in a measure. I have a column of values and i want to write a measure that tells the measure IF the value is below "a" its "Good", If its between number a & b is "Okay", and if its greater than b its "Bad".

This is how i would do it in excel but i keep missing terribly in Power Bi

=IF(Value<=800,"Good",IF(AND(Value>800,Value<1288.5),"Okay",IF(Value>1288.5,"Bad")))

the Tabel = Data

the Column = Values

Thank you,

[harshnathani]

Hi @cflynn_29 ,

You can also use the switch statement.

StatusMeasure =
VAR s =
    SUM ( Data[Value] )
RETURN
    SWITCH (
        TRUE (),
        s <= 800, "Good",
        s > 800
            && s < 1288.5, "Okay",
        "Bad"
    )

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[Anonymous]

@harshnathani 

One of the conditions is redundant. You can simply write:

StatusMeasure =
VAR s = SUM ( Data[Value] )
RETURN
    SWITCH ( TRUE (),
        s <= 800, "Good",
        // This should probably be
        // <=, not <
        s < 1288.5, "Okay",
        "Bad"
    )

[harshnathani]

Hi @Anonymous ,

Thank You for pointing that out.

Still so much to learn 🙂 

Regards,

Harsh Nathani

[harshnathani]

HI @cflynn_29 ,

Did the solution work for you.

Please mark as solution for other community members to benefit.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[nandukrishnavs]

@cflynn_29 

Using Edit Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMFCK1YlWMgKyLaFsYyDb0BTEiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Value] <= 800 then "Good" else if [Value]>800 and [Value] <= 1288.5 then "Okay" else "Bad"),
    Status = #"Added Custom"{1}[Status]
in
    Status

Using DAX measure

StatusMeasure =
VAR s =
    SUM ( Data[Value] )
VAR result =
    IF (
        s <= 800,
        "Good",
        IF (
            s > 800
                && s < 1288.5,
            "Okay",
            "Bad"
        )
    )
RETURN
    result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂