Forum Discussion
IF or SWITCH
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" )
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"
)
Can I ask what would be the difference to use your code above and
Column = SWITCH ( 'Table'[Field1] ,"AAA", "RETAIL", "BBB", "ONLINE", SEARCH ( "CCC", 'Table'[Field2], 1, 0 ) = 1, "RETAIL", "DDD", "RETAIL", "UNKNOWN" )
I am facing a similar problem. I have tried different methods, but they are either returning an incorrect total or only pick up part of the categories. I need to use different calculations in one of the switches.
To be more specific,
Column = SWITCH ( 'Table'[Field1] ,"AAA", 1, "BBB", if('Table'[Field2] = "CCC", 1, 0) , "DDD", 1, BLANK() )
When I use 'Table'[Field1] and 'Table'[Field2] as categories for a matrix, the subtotal for 'Table'[Field1]= "BBB" couldn't return the correct result. Everything else is working.
Any help on this problem would be highly appreciated.
Thank you very much.
Hi. Filed1 is a measure or Column. I see that in Field1 you tell bi to bring a column.