Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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")
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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")
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]
    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      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.