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 e...
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.

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.