Forum Discussion

aabati's avatar
aabati
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    aabatiThis blog by Marco Russo outlines the direct equivalent. his sqlbi website and daxpatterns website is an exceptional resource.

  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago
    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

     

    aabati

    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"
)

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago
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

 

aabati

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"
)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.