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:

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.

ManjariThiru's avatar
ManjariThiru
Regular Visitor
8 years ago

Hi  @aabati

 

Hope you are doing well, 

Requesting your help on creating a DAX  switch formula to get the difference percentage for different quarters 

This is the table I am working called called "Data"

my trial: i created a measure for one condiiton which is not working not sure how to do the rest : Clicks_Difference%

 

SWITCH( TRUE(), SUM(FILTER(Data, Data[Quater] = "FY18-Q2"), Data[Clicks]) > SUM(FILTER(Data, Data[Quater] = "FY18-Q1"), Data[Clicks]), (CONCATENATE("+",CONCATENATE(Sumx(FILTER(Data, Data[Quarter] = "FY18-Q2"), Data[Clicks])/DISTINCTCOUNT(Data[Employee])) - (Sumx(FILTER(Data, Data[Quarter] = "FY18-Q1"), Data[Clicks])/DISTINCTCOUNT(Data[Employee])), "%"), BLANK())

 


  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    ManjariThiru  did you resolver? If yes, i would like to know because i'm with the same problem. Thanks!