topic Re: Calculated column using switch in Developer

Calculated column using switch

wyller — Mon, 11 Mar 2024 16:25:49 GMT

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",

Re: Calculated column using switch

tackytechtom — Mon, 11 Mar 2024 20:40:01 GMT

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/

Re: Calculated column using switch

Anonymous — Tue, 12 Mar 2024 07:29:02 GMT

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.