Conditional formatting measure not working

[JoshP11]

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

[Anonymous]

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]

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 · Accepted solution]

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.

[dharmendars007]

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]

Hello @dharmendars007  - thank you for your suggestion!

Its now showing this error, any ideas?

Thanks,

Josh