if-statementdax

DAX Formula to replace nested if statement on measures


I have 2 measure where it needs to have two conditions as it will apply a different if statement

  1. if measure 1 is more than 0, the formula should be If('Measure1' > 'Measure2', "Narrow", "Widen")
  2. If measure 1 is less than 0, the formula should be If('Measure1'<'Measure2',"Widen","Narrow")

How could I put this into the PowerBI Dax formula?


Solution

  • The simplest way would be do to it using a SWITCH so it easily readible and easy to change but I'm sure it could be written in a more compact format as well.

    Measure =
        SWITCH(TRUE(),
        [Measure1]>0,if([Measure1]>[Measure2],"Narrow","Widen"),
        [Measure1]<0,if([Measure1]<[Measure2],"Widen","Narror")
    

    Another way:

    Measure = 
        SWITCH(
            TRUE(),
            [Measure1] > 0 && [Measure1] > [Measure2], "Narrow",
            [Measure1] > 0 && [Measure1] <= [Measure2], "Widen",
            [Measure1] < 0 && [Measure1] < [Measure2], "Widen",
            [Measure1] < 0 && [Measure1] >= [Measure2], "Narrow"
        )
    

    This also would work I think, if I got the logic right but it's just an example how not to do it (would still work I think):

    Measure = 
        IF(
            [Measure1] * [Measure2] > 0, 
            IF(ABS([Measure1]) > ABS([Measure2]), "Narrow", "Widen"), 
            IF([Measure1] > 0, "Narrow", "Widen")
        )
    

    And just for fun:

    Measure = 
        IF(
            RANKX(UNION(ROW("Value", [Measure1]), ROW("Value", [Measure2])), [Measure1]) = 1,
            IF([Measure1] > 0, "Narrow", "Widen"),
            IF([Measure1] < 0, "Widen", "Narrow")
        )