Forum Discussion
Switch / Case multiple results
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!
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.
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
Anonymous I don't need your data, I just need data that simulates your data.