Switch / Case multiple results

[Anonymous]

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). I've found that Switch( True(),... ) only wants to find one criteria but I have overlapping cases where more than one case is true.

Here's generally what I've got so far:

Error Message = 
SWITCH(
TRUE(),
'Data'[Start_Date]=BLANK(),"Start Date is blank",
'Data'[Status]="Red","Status is Red",
//among several dozen other columns
"No Errors")

What's my alternative since if/elif doesn't seem to be availabile in dax? Any ways to cycle back through the cases? Ultimately I'd like to find a way to concatenate the various error messages with commas into a single column (per row) in a table.

Any ideas from the community? Thanks. 

[Greg_Deckler]

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

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

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

@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

[Greg_Deckler]

@Anonymous I don't need your data, I just need data that simulates your data.