Forum Discussion
CASE Complex statement in DAX
Anonymous , You can try like
New field = SWITCH (
TRUE (),
table1[Category] in ("hair-color","hair-care","hair-style"), "LUXE",
"Other"
)But if need to search then you need use search, check
Anonymous , You can try like
New field = SWITCH (
TRUE (),
table1[Category] in ("hair-color","hair-care","hair-style"), "LUXE",
"Other"
)
But if need to search then you need use search, check
Hi Amit,
It is throwing an error of Operator or expressions '()' is not suported in this context.
Below ways can however work:
NEW_FIELD = SWITCH (
TRUE (),
CONTAINSSTRING(Table1[Category],"hair-color"), "LUXE",
CONTAINSSTRING(Table1[Category],"hair-care"),"LUXE",
CONTAINSSTRING(Table1[Category],"hair-style"), "LUXE"
)and
NEW_FIELD = SWITCH (
TRUE (),
CONTAINSSTRING(Table1[Category],"hair-color")||CONTAINSSTRING(Table1[Category],"hair-care")||
CONTAINSSTRING(Table1[Category],"hair-style"), "LUXE"
)But I would want to see , if we can give multiple values in "IN" Statement so to avoid giving column name, using CONTAINSSTRING function again and again , like it works in Tableau by using "|" symbol and dont need to give column name repeatedly WHEN REGEXP_MATCH(Field 1, "*.Hair-Color.*|*.Hair-Care.*|.*Hair-Style.*") THEN "Hair"
Hi Anonymous ,
You can use Amit's answer, but you need to use curly braces (not standard paretheses) around the 'IN' list, as it is a list. Amit's calculation updated would look like this:
New field = SWITCH ( TRUE (), table1[Category] IN {"hair-color", "hair-care", "hair-style"}, "LUXE", table1[Category] IN {"Skin-Sun", "Skin-Face", "Skin-Body"}, "SKIN", "Other" )I already suggested the CONTAINSSTRING option but Amit's method, correctly implemented, is far more performant I believe.
Pete
