Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Switch / Case multiple results

Hello all,    I'm attempting to make an "error message" column in a table that can read multiple criteria and result the reasons there's an error message for the given project (rows in the table). ...
concatenate
switch
true()
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Anonymous Well one way would be this:

Error Message = 
SWITCH(
TRUE(),
'Data'[Start_Date]=BLANK() && 'Data'[Status]="Red","Start Date is blank and red",
'Data'[Start_Date]=BLANK(),"Start Date is blank",
'Data'[Status]="Red","Status is Red",
//among several dozen other columns
"No Errors")
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Greg_Deckler Only problem is that if I hardcode every possibility, it will be very difficult to firstly add all the columns I need and secondly to update this as more situations require error messages. I have about 15 situations thus far and I'm still gathering requirements for this project. Any ideas on how to make this more dynamic and robust than just hardcoding every combination? Thanks!

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      4 years ago

      Anonymous To form a reasonable approach, I would need to better understand your requirements. Can you provide some sample data and then the current rules that you have collected? I could formulate an approach then that would be specific. That said, there are constructs for emulating for loops in DAX. For Loop - Microsoft Power BI Community. I could envision an approach where you have your rules table and for each rule you would perform a specific check and add the result of that check as a column using ADDCOLUMNS. So, for example, take your SWITCH statement as the basis for your ADDCOLUMNS value return but modify it so that it functions based upon rule ID. Something like:

      Errors Message = 
  VAR __Table = 
    ADDCOLUMNS(
      'RulesTable',
      "Error",
        SWITCH(TRUE(),
          [RuleID] = 1, IF('Data'[Start_Date]=BLANK(),"Start Date is blank",BLANK()),
          [RuleID] = 2, IF('Data'[Status]="Red","Status is Red",BLANK())
        )
    )
RETURN
  CONCATENATEX(__Table,[Error],,", ")

      This would return a single text string of all of the matching error conditions.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Greg_Deckler Thanks for sharing that for loop resource. The columns I'm looking at are mainly in the same table and are simple logical things I can check with an IF statement. Can't share details but it's just a bunch of projects with many measurements stored in different columns. 

         

        Where do I instantiate the [RuleID] columns? Should I create a RulesTable separate from the one constructed in the variable table that we're looping through? Thanks