Forum Discussion
IF or SWITCH
Hi !
I am trying to implement in DAX the following case statement:
(CASE
WHEN Field1 = "AAA" then "RETAIL"
WHEN Field1 = "BBB" then "ONLINE"
WHEN Field2 like "CCC%" then "RETAIL"
WHEN Field2 = "DDD" then "RETAIL"
ELSE ("UNKNOWN")
END)
As you can see I am using 2 fields, field1 and fiel2 therefore I cannot use the SWITCH function as its evaluating one field
Also, if I use the IF I didnt manage to find the syntax for the like "CCC%" espression.
Many thanks in advance for your help!
Antonio
aabatiThis blog by Marco Russo outlines the direct equivalent. his sqlbi website and daxpatterns website is an exceptional resource.
aabati wrote:
Hi !
I am trying to implement in DAX the following case statement:
(CASE
WHEN Field1 = "AAA" then "RETAIL"
WHEN Field1 = "BBB" then "ONLINE"
WHEN Field2 like "CCC%" then "RETAIL"
WHEN Field2 = "DDD" then "RETAIL"
ELSE ("UNKNOWN")
END)
As you can see I am using 2 fields, field1 and fiel2 therefore I cannot use the SWITCH function as its evaluating one field
Also, if I use the IF I didnt manage to find the syntax for the like "CCC%" espression.
Many thanks in advance for your help!
Antonio
You can still use SWITCH, just in a little tricky way.
Column = SWITCH ( TRUE (), 'Table'[Field1] = "AAA", "RETAIL", 'Table'[Field1] = "BBB", "ONLINE", SEARCH ( "CCC", 'Table'[Field2], 1, 0 ) = 1, "RETAIL", 'Table'[Field2] = "DDD", "RETAIL", "UNKNOWN" )
13 Replies
aabati wrote:
Hi !
I am trying to implement in DAX the following case statement:
(CASE
WHEN Field1 = "AAA" then "RETAIL"
WHEN Field1 = "BBB" then "ONLINE"
WHEN Field2 like "CCC%" then "RETAIL"
WHEN Field2 = "DDD" then "RETAIL"
ELSE ("UNKNOWN")
END)
As you can see I am using 2 fields, field1 and fiel2 therefore I cannot use the SWITCH function as its evaluating one field
Also, if I use the IF I didnt manage to find the syntax for the like "CCC%" espression.
Many thanks in advance for your help!
Antonio
You can still use SWITCH, just in a little tricky way.
Column = SWITCH ( TRUE (), 'Table'[Field1] = "AAA", "RETAIL", 'Table'[Field1] = "BBB", "ONLINE", SEARCH ( "CCC", 'Table'[Field2], 1, 0 ) = 1, "RETAIL", 'Table'[Field2] = "DDD", "RETAIL", "UNKNOWN" )thank you very much for both the replies, very very useful
This is a fabtastuc community, always very useul thanks again guys !
Antonio
aabati wrote:
thank you very much for both the replies, very very useful
This is a fabtastuc community, always very useul thanks again guys !
Antonio
It is glad that we can help. Only thing that you'll have to notice, just always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
@aabati This has been really helpful.
Column = SWITCH ( TRUE (), 'Table'[Field1] = "AAA", "RETAIL", 'Table'[Field1] = "BBB", "ONLINE", SEARCH ( "CCC", 'Table'[Field2], 1, 0 ) = 1, and 'Table'[Field1] like '%yyy%', "RETAIL", 'Table'[Field2] = "DDD", "RETAIL", "UNKNOWN" )What if I wanted to add one more condition to the search function(the highlighted part of text above) and both must be true then return 'Retail'?
Hi ERIC.
I have a similar situation.
The case is: if Column named Branch equals 03 and 04 this should add Spain, and if equals 31, 34, 82,83, 85, 89, 40 then it should bring France, rest Unknown.
I have tried if / if(or (if(and - but it does not work as thoes function only allow a mazimum of 2 arguments, and as you can see I have several.
I have tried Switch as per your post, but i am not sure how to apply the search function.
Hope you can help and looking forward to it.
aabatiThis blog by Marco Russo outlines the direct equivalent. his sqlbi website and daxpatterns website is an exceptional resource.
Hi. I have a similar situation.
In common words i want something like: if Column A =reg and if column B=lab then Column 00, else unknown
I am usuing swithch command:
SWITCH(TRUE();'Table'[Column A] in {"reg"}; 'Table'[Column 00] ; Table'[Column A] in {"lab"}; 'Table'[Column 00]; unknown)so, using the commas ; it brinngs both results creating 2 row.Does anyone know how to fix this? And maybe if function works better?Looking forward. Thanks!!!
