Forum Discussion

dkenniston's avatar
dkenniston
Frequent Visitor
3 years ago
Solved

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

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!
DAX
Help
  • dkenniston's avatar
    dkenniston
    3 years ago

    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!

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      dkenniston
      Frequent Visitor
      3 years ago

      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's avatar
        dkenniston
        Frequent Visitor
        3 years ago

        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!