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

  • aabati's avatar
    aabati
    Frequent Visitor
    9 years ago

    thank you very much for both the replies, very very useful

    This is a fabtastuc community, always very useul thanks again guys !

     

    Antonio

     

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

       

      aabati

      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.

      • bukhari1979's avatar
        bukhari1979
        Frequent Visitor
        8 years ago

        Hi Eric_Zhang First of all your reply was very helpful. However Can we use && (AND) , || OR operator in switch case to reach a decission.

        FOr example

         

        'Table1'[Field1] = "AAA" && 'Table2'[Field1] = "99", "RETAIL",

         

         

        will be grateful

  • lukaspowerbi's avatar
    lukaspowerbi
    Helper II
    8 years ago

    @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'?  

  • 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. 

  • leahYan's avatar
    leahYan
    Advocate II
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi. Filed1 is a measure or Column. I see that in Field1 you tell bi to bring a column.