How to get a measure to return multiple rows for every condition evaluated as TRUE

[dkenniston]

Hi there,

I'm writing a DAX measure that I'd like to return multiple rows for every condition evaluated as TRUE.

I have the following, for example:

VAR maritalStatus = SELECTEDVALUE('New Client Intake Responses'[Marital Status])
VAR atRiskOfDivorce = SELECTEDVALUE('FamilyLife Profile'[Change in marital status (divorce)])
VAR atRiskOfWidowhood = SELECTEDVALUE('FamilyLife Profile'[Change in marital status (widowhood)])
RETURN
SWITCH(
    TRUE(),
    maritalStatus = "Married" && atRiskOfDivorce IN {"Currently experiencing", "Anticipate short- to mid-term", "Anticipate long-term"}, "Div Risk (set tax status to MFS)",
    maritalStatus = "Married" && atRiskOfWidowhood IN {"Currently experiencing", "Anticipate short- to mid-term"}, "Anticipated loss of spouse"
)

In the case above, it's possible for both of the criteria to be true. And if both are true, I'd like this measure to return both

"Div Risk (set tax status to MFS)" and "Anticipated loss of spouse". But as written, it only returns the first criteria evaluated as true: "Div Risk (set tax status to MFS)".

What would be the best way to go about this? Would really prefer to keep these in a single measure, because it'll make the report look cleaner..

Thanks!

[amitchandak]

@dkenniston , based on what I got, you need a measure like

VAR maritalStatus = SELECTEDVALUE('New Client Intake Responses'[Marital Status])
VAR atRiskOfDivorce = SELECTEDVALUE('FamilyLife Profile'[Change in marital status (divorce)])
VAR atRiskOfWidowhood = SELECTEDVALUE('FamilyLife Profile'[Change in marital status (widowhood)])
RETURN Countrow(filter(Table, not(Isblank(
SWITCH(
TRUE(),
maritalStatus = "Married" && atRiskOfDivorce IN {"Currently experiencing", "Anticipate short- to mid-term", "Anticipate long-term"}, "Div Risk (set tax status to MFS)",
maritalStatus = "Married" && atRiskOfWidowhood IN {"Currently experiencing", "Anticipate short- to mid-term"}, "Anticipated loss of spouse"
))) ) )

[dkenniston]

Hi @amitchandak, thank you so much for the response!

Just tried adding COUNTROWS(FILTER('New Client Intake Responses', NOT(ISBLANK(  

before the SWITCH statement. 

It returned the number 1. 

Open to other suggestions though - thanks again!

[dkenniston · Accepted solution]

I think getting to an answer would require more sophisticated DAX than I'm able to come up with, so I've modified the data model instead. Closing this one out. Thanks anyway for all who viewed and to @amitchandak for the opening shot!