Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
@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"
))) ) )
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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
15 |