Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Query With IF Condition MisBehaves - Power BI

Hi,

 

I am using the below DAX to create a Indicator color. 

 

SCHEDULEIndicatorColor = IF('pjrep MSP_EpmProject_UserView'[Deviation] < -15,"#FF0000",IF('pjrep MSP_EpmProject_UserView'[Deviation] >= -15 && 'pjrep MSP_EpmProject_UserView'[Deviation] < -5, "#D3D3D3",IF('pjrep MSP_EpmProject_UserView'[Deviation] > -5,"#33AF81","#33AF81")))
 
My target is
If Deviation is lesser than -15 then it gives Red Color.
If Deviation is greater than and equal to -15 and lesser than -5 then it gives yellow color.
If Deviation is greater than -5 then it gives Green color.
If Deviation is not in any of this condition then also it gives green color. 
 
However I could see sometimes the color is appearing as light grey or something. What could be the problem ? There are some blank and null values too in deviation field. Will it cause any issue ?
How to handle it ?
Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    5 years ago

    Hey Anonymous ,

     

    besides what PhilipTreacy already mentioned, there is a little in your formula, as it states ... <-5 and on the side > -5. This means being exact -5 is not defined.

    You also want to consider to cache the reference to this column

    pjrep MSP_EpmProject_UserView'[Deviation]

    to a variable, as each reference evaluates the column value, using a variable will improve a performance.

     

    The little DAX statement below, is using a variable (the performance thing) and is also using the the SWITCH function as this can help to make more complex conditions more readable:

    check = 
var __amount = 'Table'[amount]
return
SWITCH( TRUE()
    , __amount < 0 , "red"
    , __amount = 0 && __amount < 2, "yellow"
    , __amount >= 2 , "green"
    , "orange"
)

    The screenshot below shows, that blank values will be treated as Zero:

     

    Hopefully, this helps to tackle your challenge.

     

    Regards,

    Tom

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    Hey Anonymous ,

     

    besides what PhilipTreacy already mentioned, there is a little in your formula, as it states ... <-5 and on the side > -5. This means being exact -5 is not defined.

    You also want to consider to cache the reference to this column

    pjrep MSP_EpmProject_UserView'[Deviation]

    to a variable, as each reference evaluates the column value, using a variable will improve a performance.

     

    The little DAX statement below, is using a variable (the performance thing) and is also using the the SWITCH function as this can help to make more complex conditions more readable:

    check = 
var __amount = 'Table'[amount]
return
SWITCH( TRUE()
    , __amount < 0 , "red"
    , __amount = 0 && __amount < 2, "yellow"
    , __amount >= 2 , "green"
    , "orange"
)

    The screenshot below shows, that blank values will be treated as Zero:

     

    Hopefully, this helps to tackle your challenge.

     

    Regards,

    Tom

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi Anonymous 

    #D3D3D3 is grey, not yellow.

    The blanks and nulls will end up as the default #33AF81 beacuse they don't match any of the other conditions.

    Regards

    Phil

  • Angith_Nair's avatar
    Angith_Nair
    Continued Contributor
    5 years ago

    Hi Anonymous ,

    Hope you are doing good.

    Try the following measure:

    SCHEDULEIndicatorColor =
IF (
    'pjrep MSP_EpmProject_UserView'[Deviation] < -15,
    "#FF0000",
    IF (
        AND (
            'pjrep MSP_EpmProject_UserView'[Deviation] >= -15,
            'pjrep MSP_EpmProject_UserView'[Deviation] < -5
        ),
        "#FFFF00",
        IF ( 'pjrep MSP_EpmProject_UserView'[Deviation] > -5, "#33AF81", "#33AF81" )
    )
)