Forum Discussion
Need help with my custom column switch statement based on multiple criteria
Hello,
Im trying to create a custom column that populates a certain value based on multiple criteria (values in other columns). I want it to populate the word "New Case" if the "status column" doesnt equal "Return" AND the value column equals HIGH and the state is not one of 5 states. I tried the formula below and it isnt working. any help would be appreciated
Switch(
TREU(),
Table name[Status column]<>"Return" && Table name[value column] = " HIGH" && Table name[Status column] ="NY" || Table name[Status column] ="CA" || Table name[Status column] ="NJ" || Table name[Status column] ="MD" || Table name[Status column] ="TX","Normal","New Case")
IF( Table name[Status column]<>"Return" && Table name[value column] = " HIGH" && NOT(Table name[Status column] in {"NY", "CA", "NJ", "MD", "TX"}) ,"Normal","New Case") OR IF(Table name[Status column]<>"Return" && Table name[value column] = " HIGH" && (Table name[Status column] ="NY" || Table name[Status column] ="CA" || Table name[Status column] ="NJ" || Table name[Status column] ="MD" || Table name[Status column] ="TX"),"Normal","New Case")No need for the switch statement if there's only 2 outcomes.
You can rewrite the Status parts with the in operator to improve readability - read more here: https://www.sqlbi.com/articles/the-in-operator-in-dax/
Or just surround the whole thing with a brackets so that they are evaluated as a group
1 Reply
IF( Table name[Status column]<>"Return" && Table name[value column] = " HIGH" && NOT(Table name[Status column] in {"NY", "CA", "NJ", "MD", "TX"}) ,"Normal","New Case") OR IF(Table name[Status column]<>"Return" && Table name[value column] = " HIGH" && (Table name[Status column] ="NY" || Table name[Status column] ="CA" || Table name[Status column] ="NJ" || Table name[Status column] ="MD" || Table name[Status column] ="TX"),"Normal","New Case")No need for the switch statement if there's only 2 outcomes.
You can rewrite the Status parts with the in operator to improve readability - read more here: https://www.sqlbi.com/articles/the-in-operator-in-dax/
Or just surround the whole thing with a brackets so that they are evaluated as a group