CASE Complex statement in DAX

[avargas--]

Hello! 🙂 

I am trying to "translate" a complex case statement in DAX. I have tried 'Switch function' and it's OK:

New field = SWITCH (

        TRUE (),

table1[Category] = "hair-color", "LUXE",

table1[Category] = "hair-care", "LUXE",

table1[Category] = "hair-style", "LUXE",

...etc

"Other"

)

What's the problem? When within the case statement I have more options for the same string:

CASE
WHEN REGEXP_MATCH(Field 1, ".*Hair-Color.*|.*Hair-Care.*|.*Hair-Style.*") THEN "Hair"
WHEN REGEXP_MATCH(Field 1, ".*Skin-Sun.*|.*Skin-Face.*|.*Skin-Body.*") THEN "Skin"

... etc

ELSE "Other"

END

How can I translate every of these options: ".*Skin-Sun.*|.*Skin-Face.*|.*Skin-Body.*"|...etc" within my Switch function in DAX?

Thanks so much in advance! 🙂 

[BA_Pete]

Hi @avargas-- ,

This might work?

_newField =
SWITCH(
  TRUE(),
  CONTAINSSTRING(table1[Category], "Hair"), "Hair",
  CONTAINSSTRING(table1[Category], "Skin"), "Skin",
  ...
)

Pete

[amitchandak · Accepted solution]

@avargas-- , 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

https://youtu.be/mZt0HJw4gjQ

[Anonymous]

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"

[BA_Pete]

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