Calculated column using switch

[wyller]

I'm trying to create a calculated column using switch();

This 1st code worked fine. The 2nd throws an error. What am I doing wrong?

Aging =
VAR _daycount = DATEDIFF([Created], TODAY(), DAY)
RETURN
SWITCH(
TRUE(),
[CurrentStatus] = "Complete", "Not Applicable",
_daycount <= 30, "0-30 days old",
_daycount > 31 && _daycount <=60, "31-60 days old",
_daycount > 61 && _daycount <=90, "61-90 days old",
"Lifetime"
)
............

Aging2 =
VAR _daycount = DATEDIFF([Created], TODAY(), DAY)
RETURN
SWITCH(
TRUE(),
[CurrentStatus] = "In Process", "In Review", "Pending", "Submitted",
_daycount <= 30, "0-30 days old",
_daycount > 31 && _daycount <=60, "31-60 days old",
_daycount > 61 && _daycount <=90, "61-90 days old",
"Lifetime"
)

The difference is in the 1st, there are only 2 conditions:

       [CurrentStatus] = "Complete", "Not Applicable",

In the 2nd, there are 4:

       [CurrentStatus] = "In Process", "In Review", "Pending", "Submitted",

[Anonymous · Accepted solution]

Hi  @wyller ,

Thanks @tackytechtom  for sharing, I have something to add here as well.

Is Dax's error message of the following form:

Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

You can try using the following dax.

Aging2 = 
VAR _daycount = DATEDIFF([Created], TODAY(), DAY)
RETURN
SWITCH(
[CurrentStatus] IN{ "In Process", "In Review", "Pending", "Submitted"},
_daycount <= 30, "0-30 days old",
_daycount > 31 && _daycount <=60, "31-60 days old",
_daycount > 61 && _daycount <=90, "61-90 days old",
"Lifetime"
)

Best Regards,

Liu Yang

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

[tackytechtom]

Hi @wyller ,

I think the way you want to use the SWITCH() function follows this logic:

SWITCH(
TRUE,

Condition1, OutputIfCondition1IsMet,

Condition2, OutputIfCondition2IsMet,

Condition3, OutputIfCondition3IsMet,

...,

OutputIfNoneOfTheAboveConditionIsMet

)

That is why I would say the second DAX code runs into a syntax error, since there is no condition after "In Review". I would also say the first DAX code might be semantically flawed since the output should result in "Not Applicable" if  [CurrentStatus] = "Complete", which I am not sure, whether that is what you want.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/