cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
texmexdragon Post Partisan
Post Partisan

Multi conditional switch statement

Hello  -  I have a situaiton where I have a Switch statment that only partially works.    I am curious if there is a better way to write this (that actually works!) or perhaps use  VAR.   

 

My use case is that I have returns (we call them RMA Orders) and these orders can have multiple lines to them.  Or just a single line.

 

Since these are returns, there is a problem code associated with them (T1 - T7).     One order can sometimes have multiple problem codes, even if it is just a single line Order.     Or, and Order can have multiple lines to it, and each line has a different problem code.

 

If an Order (regardless of the number of lines) has just one problem code associated with it, I need the newly created column to say "Single" on that row that contains the Order number.      If the Order has multiple product codes associated (again...whether it is a one line order or several lines), then each row for that Order needs to say "Multiple".  

 

Although not completely correct, you can get some idea of how this should look by looking at the first two rows below.   Order RMA-000873 has two lines to it.    Each line has a different product code associated with it  (and could in theory, have many different product codes associated with it).      The current formula does correctly say "Multiple", but there are many other rows that incorrectly say Single  or   Multiple  so I know the formula is not working.   

 

Ideally, I wish I could put both  the  &&   and   ||   operators together as this would logically fit what I need.   In other words, if the Order = just one product code, then Single.    If it equals 2 or more, then Multiple.   

 

MULTIPLE.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Multi conditional switch statement

One more ) right before your RETURN statement

 

Multiple or Single = 
  VAR __Table = 
    DISTINCT(
      SELECTCOLUMNS(
        FILTER(
          ALL('Table'),
          [RMA] = EARLIER([RMA])
        ),
        "__Problem Code",[Problem Code]
      )
    )
RETURN
  IF(COUNTROWS(__Table) > 1,"Multiple","Single")

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

Re: Multi conditional switch statement

So, no sample text data provided and I don't like typing so going to wing this DAX calculation, apologies in advance for syntax errors. This is for a column.

 

Multiple or Single = 
  VAR __Table = 
    DISTINCT(
      SELECTCOLUMNS(
        FILTER(
          ALL('Table'),
          [RMA] = EARLIER([RMA])
        ),
        "__Problem Code",[Problem Code]
    )
RETURN
  IF(COUNTROWS(__Table) > 1,"Multiple","Single")

 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

texmexdragon Post Partisan
Post Partisan

Re: Multi conditional switch statement

Thanks Greg.   I do get two errors.     When I hover over the __Table it says  "Parameter is not the correct type.   Cannot find name __Table.

 

And it says the syntax for Return is incorrect.   

 

Any idea why this is happening?  

 

multiple single.png

Super User IV
Super User IV

Re: Multi conditional switch statement

In your VAR statement, replace the comma at the end with a )

 

I believe that should fix it. 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

texmexdragon Post Partisan
Post Partisan

Re: Multi conditional switch statement

Thanks Greg  -  Sorry to trouble you but I still got an error.     The red lines are saying 1) a syntax error with Return, and 2) the other two red lines say these are "Unexpected Expressions". 

 

So close....!

 

 

Greg.png

Super User IV
Super User IV

Re: Multi conditional switch statement

One more ) right before your RETURN statement

 

Multiple or Single = 
  VAR __Table = 
    DISTINCT(
      SELECTCOLUMNS(
        FILTER(
          ALL('Table'),
          [RMA] = EARLIER([RMA])
        ),
        "__Problem Code",[Problem Code]
      )
    )
RETURN
  IF(COUNTROWS(__Table) > 1,"Multiple","Single")

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

texmexdragon Post Partisan
Post Partisan

Re: Multi conditional switch statement

That did it Greg!!  Thank you so much.    The Power Bi forum is seriously amazing.   Everyone is so helpful.   And a great learning resource as other more experienced users help folks like me with these formulas....so that we may study and learn from them. 

 

Good stuff!

texmexdragon Post Partisan
Post Partisan

Re: Multi conditional switch statement

One last question...just trying to understand the "why" of this part of the formula.   I undersand all of the rest, but do not understand how the "__Problem Code", [Problem Code]  works?     What is the __Problem Code as it does not seem to be a VAR?    Many thanks again for your help.

 

          [RMA] = EARLIER([RMA])
        ),
        "__Problem Code",[Problem Code]
Super User IV
Super User IV

Re: Multi conditional switch statement

Sure, when you use SELECTCOLUMNS you have to specify a name for the column you are "creating" and then what column to select. So the name of the column in the calculation "__Produce Code" and the column that is selected is the "Problem Code" column in the table. SELECTCOLUMNS returns a table with the column names that you specify and those columns have the values of the column in the original table you are selecting from.

 

Now, the next question that you are likely to ask is why I didn't just use:

 

"Problem Code",[Problem Code]

 

And just name the column the same name as before. The reason is that this can become VERY confusing as to which "Problem Code" column you are referring to, especially with DAX's intellisense. I find it better to prefix things that I create as VAR's and such with "__". This ensures that I know what the heck I am referring to later in my DAX code. When you write complex DAX, trust me, it is very helpful to keep things straight in some manner like this. 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors