Forum Discussion

wyller's avatar
wyller
New Member
2 years ago
Solved

Calculated column using switch

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

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    2 years ago

    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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.