Forum Discussion

JoshP11's avatar
JoshP11
Helper II
1 year ago
Solved

Conditional formatting measure not working

Hello,

 

I'm using the below measure for some conditional formatting, and on one hand it's working as expected, however I can't get it to work for blank values, the colour seems to replicate what ever the other column is showing, but if there is no value then it should be white.

 

Can anyone help?

 

ValueColor =
VAR TestAve = [AverageValue]
VAR TestStdDev = [StdDevValue]
VAR TestValue = MAX('Unpivot Data'[Value])
RETURN
SWITCH(
    TRUE(),
    ISBLANK(TestValue), "#FFFFFF",  -- White color for blank values
    TestValue >= TestAve + TestStdDev, "#53E17E",
    TestValue >= TestAve + 0.5 * TestStdDev && TestValue < TestAve + TestStdDev, "#FFFF47",
    TestValue >= TestAve - TestStdDev && TestValue < TestAve + 0.5 * TestStdDev, "#FFB700",
    TestValue <= TestAve - TestStdDev, "#FF1414",
    "#FFFFFF"  -- Default to white if none of the conditions are met
)
 
As you can see, it's working on the right side, but when the left side is blank it mirrors the right side colour.
 

 

 

Is there something I'm missing? I've checked the format of value and that's fine, and in the data the cells all appear as blank.

 

Thanks,

Josh

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ,

    The TestValues variable should get the Dynamic_Recent value. Try using the following dax formula.

    ValueColor =
VAR TestAve = [AverageValue]
VAR TestStdDev = [StdDevValue]
VAR TestValue = SELECTEDVALUE('Unpivot Data'[Value], BLANK())
RETURN
SWITCH(
    TRUE(),
    ISBLANK(TestValue), "#FFFFFF",  -- White color for blank values
    TestValue >= TestAve + TestStdDev, "#53E17E",
    TestValue >= TestAve + 0.5 * TestStdDev && TestValue < TestAve + TestStdDev, "#FFFF47",
    TestValue >= TestAve - TestStdDev && TestValue < TestAve + 0.5 * TestStdDev, "#FFB700",
    TestValue <= TestAve - TestStdDev, "#FF1414",
    "#FFFFFF"  -- Default to white if none of the conditions are met
)

     

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    Hello JoshP11 

     

    Fix: Add a condition to check both ISBLANK() and if the value equals an empty string ("").

     

    SWITCH(
    TRUE(),
    ISBLANK(TestValue) || TestValue = "", "#FFFFFF", -- White for blanks
    TestValue >= TestAve + TestStdDev, "#352E1E",
    TestValue >= TestAve + 0.5 * TestStdDev && TestValue < TestAve + TestStdDev, "#FFFF47",
    TestValue >= TestAve - TestStdDev && TestValue < TestAve + 0.5 * TestStdDev, "#FFB700",
    TestValue <= TestAve - TestStdDev, "#FF1414",
    "#FFFFFF" -- Default white)

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

    • JoshP11's avatar
      JoshP11
      Helper II
      1 year ago

      Hello dharmendars007  - thank you for your suggestion!

       

      Its now showing this error, any ideas?

       

       

       

      Thanks,

      Josh

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi JoshP11 ,

    Based on the error description, the DAX function don’t compare values of type number with type text.

    Try using the following DAX formula.

    ValueColor =
VAR TestAve = [AverageValue]
VAR TestStdDev = [StdDevValue]
VAR TestValue = MAX('Unpivot Data'[Value])
RETURN
SWITCH(
   TRUE(),
   TestValue = 0, "#FFFFFF",  -- White color for blank values
   TestValue >= TestAve + TestStdDev, "#53E17E",
   TestValue >= TestAve + 0.5 * TestStdDev && TestValue < TestAve + TestStdDev, "#FFFF47",
   TestValue >= TestAve - TestStdDev && TestValue < TestAve + 0.5 * TestStdDev, "#FFB700",
   TestValue <= TestAve - TestStdDev, "#FF1414",
   "#FFFFFF" -- Default to white if none of the conditions are met
)

    Then, select the conditional measure in the background color.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • JoshP11's avatar
      JoshP11
      Helper II
      1 year ago

      Hi Anonymous  - thank you for taking time to reply! I have tried updating the DAX but it's still not working.

       

      I've attached this screenshot here to help demonstrate what seems to be happening.

       

      Left table: I have applied the conditional formatting only to the recent value, and even though it is blank it is returning green. I did not apply any formatting to the beyond column.

       

       

      Right table: I have applied conditonal formatting to beyond and it returns green (as expected).

       

       

      In the left table, it seems as though the recent is picking up the value from beyond and therefore returning the green colour background.

       

      Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi ,

        The TestValues variable should get the Dynamic_Recent value. Try using the following dax formula.

        ValueColor =
VAR TestAve = [AverageValue]
VAR TestStdDev = [StdDevValue]
VAR TestValue = SELECTEDVALUE('Unpivot Data'[Value], BLANK())
RETURN
SWITCH(
    TRUE(),
    ISBLANK(TestValue), "#FFFFFF",  -- White color for blank values
    TestValue >= TestAve + TestStdDev, "#53E17E",
    TestValue >= TestAve + 0.5 * TestStdDev && TestValue < TestAve + TestStdDev, "#FFFF47",
    TestValue >= TestAve - TestStdDev && TestValue < TestAve + 0.5 * TestStdDev, "#FFB700",
    TestValue <= TestAve - TestStdDev, "#FF1414",
    "#FFFFFF"  -- Default to white if none of the conditions are met
)

         

         

        Best Regards,

        Wisdom Wu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.