topic Switch / Case multiple results in DAX Commands and Tips

Switch / Case multiple results

Anonymous — Wed, 08 Sep 2021 17:32:33 GMT

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.

Re: Switch / Case multiple results

Greg_Deckler — Wed, 08 Sep 2021 18:20:45 GMT

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

Re: Switch / Case multiple results

Anonymous — Wed, 08 Sep 2021 20:08:50 GMT

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!

Re: Switch / Case multiple results

Greg_Deckler — Wed, 08 Sep 2021 20:40:01 GMT

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.

Re: Switch / Case multiple results

Anonymous — Fri, 10 Sep 2021 17:15:28 GMT

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

Re: Switch / Case multiple results

Greg_Deckler — Fri, 10 Sep 2021 18:13:10 GMT

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