Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

CASE Complex statement in DAX

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! 🙂 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

    https://youtu.be/mZt0HJw4gjQ

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

    https://youtu.be/mZt0HJw4gjQ

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        BA_Pete
        Super User
        5 years ago

        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

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi Anonymous ,

     

    This might work?

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

     

    Pete