Using Range of Values with SWITCH(TRUE Function

[rbowen]

Greetings All - 

Acknowlegement: Yes, I could probably do this using Cell Elements and using a function to define font colors and icons, however, I'm experimenting with using unicharacters because of the wider range of icons available. 

I'm building a KPI style matrix and wanting to add some conditions to expand the results. Currently, I have a matrix which compares year to date sales between FY25 and FY26. It's working as expected - numbers greater than 0 are in a green font color with a unicharacter up arrow, while numbers less than 0 are in red with a unicharacter down arrow. 

I have two measures that achieve this.  The first one establishes the conditional logic defining the unicharacter symbols I want to use:

YTDvsLastYear =

VAR _CY = [YTDSales]

VAR _PY = [YTD-PY Sales]

VAR _perc = DIVIDE(_CY - _PY, _PY)

VAR _format =

SWITCH(

    TRUE(),

    _perc > 0, UNICHAR(11165) & " " & FORMAT(_perc, "0.0%"),

    _perc < 0, UNICHAR(11167) & " " & FORMAT(_perc*-1, "0.0%"),

    FORMAT(_perc, "0.0%")

)

RETURN

_format

The 2nd measure establishes the conditional formatting for the font color for each condition:

CF YTDvsLastYear =

VAR _CY = [YTDSales]

VAR _PY = [YTD-PY Sales]

VAR _perc = DIVIDE(_CY - _PY, _PY)

VAR _format =

SWITCH(

    TRUE(),

    _perc > 0, "Green",

    _perc < 0, "Red",

    "Grey"

)

RETURN

_format

This is working fine, but now I'd like to add some additional conditions that involve ranges. For example, for the first measure, I'd like to specify a greater than or equal to value for Green, a second range of values that would be Yellow, and the final condition remaining the same - any value less than 0 would be red. I'm having some trouble figuring out the syntax to achieve this and getting various errors on the second _perc function but the logic would go something like below:

YTDvsLastYear =

VAR _CY = [YTDSales]

VAR _PY = [YTD-PY Sales]

VAR _perc = DIVIDE(_CY - _PY, _PY)

VAR _format =

SWITCH(

    TRUE(),

    _perc >= 50, UNICHAR(11165) & " " & FORMAT(_perc, "0.0%"), 

    _perc  >= 0 and/or <= 49, UNICHAR(11166) & " " & FORMAT(_perc, "0.0%"),   <-----this line is throwing the errors

    _perc < 0, UNICHAR(11167) & " " & FORMAT(_perc*-1, "0.0%"),

    FORMAT(_perc, "0.0%")

)

RETURN

_format

I'm getting errors in the middle _perc line (in italics) trying to define a range. I've tried both && and || in the statement but the error says "unexpected condition". I'm obviously using incorrect syntax but not sure what the corrent statement should be. Once I have the first measure correct, it's an easy matter to adjust the second. Any pointers would be greatly appreciated. Thank you.

[SavioFerraz · Accepted solution]

Hi @rbowen,

In DAX, you must define ranges using logical operators (&&) and remember that percentages are decimal values (50% = 0.5). Your middle condition should be written like this:

_perc >= 0 && _perc < 0.5

That will fix the syntax error and correctly evaluate the range inside SWITCH(TRUE()).

Savio Ferraz | Microsoft Learning Consulting | Google Certified Trainer and Microsoft Certified Educator

Did my answer help? Mark my post as a solution or like it if you found it useful.

[rbowen]

I knew the && had to figure in there somehow. The piece I was missing was using the _perc after the &&. That worked perfectly, many thanks Savio!