IF or SWITCH

[aabati]

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

[Seth_C_Bauer · Accepted solution]

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

[Anonymous]

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!!!

[Eric_Zhang · Accepted solution]

---

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

[leahYan]

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]

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

[Anonymous]

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. 

[lukaspowerbi]

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

[aabati]

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]

---

@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]

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]

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

[bukhari1979]

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

[sampathkumar]

yeah we can use bukhari1979, check once let us know update please

[Seth_C_Bauer · Accepted solution]

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