topic Conditional DAX equivalent of M? in DAX Commands and Tips

Conditional DAX equivalent of M?

Anonymous — Thu, 30 Jul 2020 12:46:36 GMT

Hello I am trying to get the equivalent of the following M code in DAX.

if Text.Contains(Text.Lower([prod_name]),"purple") then "purple" else if Text.Contains(Text.Lower([prod_name]),"black") then "black" else if Text.Contains(Text.Lower([prod_name]),"white") then "white" else if Text.Contains(Text.Lower([prod_name]),"green") then "green" else if Text.Contains(Text.Lower([prod_name]),"yellow") then "green" else if Text.Contains(Text.Lower([prod_name]),"blue") then "Blue" else if Text.Contains(Text.Lower([prod_name]),"red") then "Red" else Null.Type

I tried this in DAX but it only prints the final condition

DAX:

Flavors = if(CONTAINS(table,table[prod_name],"purple"),"purple",IF(CONTAINS(table,table[prod_name],"black"),"black",IF(CONTAINS(table,table[prod_name],"white"),"white",if(CONTAINS(table,table[prod_name],"yellow",table[prod_name],"green"),"green",if(CONTAINS(table,v[prod_name],"red"),"Red","Blue")))))

Any idea how I could do this? Thanks!

Re: Conditional DAX equivalent of M?

Anonymous — Thu, 30 Jul 2020 13:22:36 GMT

Try the switch function

https://docs.microsoft.com/en-us/dax/switch-function-dax

Something along the lines of

Column = SWITCH([prod_name], "purple", "purple", "black", "black", "white", "white", "green", "green", "yellow", "green", "blue", "Blue", "red", "Red", "null")

Re: Conditional DAX equivalent of M?

Anonymous — Thu, 30 Jul 2020 13:12:08 GMT

Anonymous Does the value have to be a number, because this is only returning the "else" condition

Re: Conditional DAX equivalent of M?

Anonymous — Thu, 30 Jul 2020 13:23:31 GMT

should work

Column = SWITCH([prod_name], "purple", "purple", "black", "black", "white", "white", "green", "green", "yellow", "green", "blue", "Blue", "red", "Red", "null")

Re: Conditional DAX equivalent of M?

Anonymous — Thu, 30 Jul 2020 13:24:55 GMT

It's important to note, that it's ALL case sensitive + it'll include any trailing or leading spaces. for example

"black" is NOT equal to "Black" or "black " or " black" etc..

Re: Conditional DAX equivalent of M?

Anonymous — Thu, 30 Jul 2020 13:26:34 GMT

Anonymous Oh ok, I think this doesn't work in my case becuase there are multiple details in the [prod_name] column, so instead of purple it will say "purple(S)" or "purple[M]". Is there a way to integrate CONTAINS?

Re: Conditional DAX equivalent of M?

Anonymous — Thu, 30 Jul 2020 13:45:23 GMT

This worked

Flavors = SWITCH( TRUE(), ISERROR( FIND("purple", table[prod_name]))<> TRUE(), "purple", ISERROR( FIND("white", table[prod_name]))<> TRUE(), "white", ISERROR( FIND("black", table[prod_name]))<> TRUE(), "black", ISERROR( FIND("yellow", table[prod_name]))<> TRUE(), "green", ISERROR( FIND("green", table[prod_name]))<> TRUE(), "green", ISERROR( FIND("blue", table[prod_name]))<> TRUE(), "Blue", ISERROR( FIND("red", table[prod_name]))<> TRUE(), "Red","" )

Re: Conditional DAX equivalent of M?

Anonymous — Thu, 30 Jul 2020 13:53:00 GMT

The following would have also worked.
Please ensure you accept a solution to close the topic.

Column 2 = IF ( CONTAINSSTRING ( 'Table (2)'[prod_name], "purple" ), "purple", IF ( CONTAINSSTRING ( 'Table (2)'[prod_name], "black" ), "black", IF ( CONTAINSSTRING ( 'Table (2)'[prod_name], "white" ), "white", IF ( CONTAINSSTRING ( 'Table (2)'[prod_name], "green" ), "green", IF ( CONTAINSSTRING ( 'Table (2)'[prod_name], "yellow" ), "green", IF ( CONTAINSSTRING ( 'Table (2)'[prod_name], "blue" ), "Blue", IF ( CONTAINSSTRING ( 'Table (2)'[prod_name], "red" ), "Red", "null" ) ) ) ) ) ) )