Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aabati
Frequent Visitor

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

 

2 ACCEPTED SOLUTIONS

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

Eric_Zhang
Microsoft Employee
Microsoft Employee


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

Capture.PNG

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

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
Microsoft Employee
Microsoft Employee


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

Capture.PNG

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
Not applicable

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

Anonymous
Not applicable

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. 

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

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

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

 

Antonio

 


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

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

 


data.PNG

Anonymous
Not applicable

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

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

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

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.