Forum Discussion

BINewbie1's avatar
BINewbie1
Icon for Helper II rankHelper II
2 years ago
Solved

Using Multiple VARs as a Filter Context with an OR Condition

Hello,    I have a measure i'm trying to create to count enrollment references in one table, that satisfy the condition of being closed with a Successful outcome. There are two types of outcome - F...
DAX
  • BINewbie1's avatar
    BINewbie1
    2 years ago

    Anonymous  thanks for pointing me in the right direction regarding Union and Treatas. After some playing around I got the following code to work as needed:

     

    Count of Enrollments Closed With a Successful Outcome =
       
    VAR _FinancialOutcomes = SUMMARIZE(CALCULATETABLE('Financial Outcomes',
            'new_clientsurveys'[new_outcomesum_ac_financial_base] >= 1,
            NOT ISBLANK ( 'Financial Outcomes'[new_projectenrolmentlookup] ),
            REMOVEFILTERS ('Calendar')),
                [new_projectenrolmentlookup])

    VAR _SuccessfullNonFinancialOutcome = SUMMARIZE(CALCULATETABLE('Non-Financial Outcomes',
            'Non-Financial Outcomes'[new_outcomesubtype_ac] <> "Case concluded unsuccessfully",
            'Non-Financial Outcomes'[new_outcomesubtype_ac] <> "Client ceased to give instructions",
            'Non-Financial Outcomes'[new_outcomesubtype_ac] <> "Unknown Outcome",
             NOT ISBLANK ( 'Non-Financial Outcomes'[new_projectenrolmentlookup] ),
            REMOVEFILTERS ('Calendar')),
        [new_projectenrolmentlookup])

    VAR _CombinedOutcomes =
        UNION(
            _FinancialOutcomes,
            _SuccessfullNonFinancialOutcome)

    RETURN
        CALCULATE (
            [Count of Enrollments Closed],
            TREATAS (
                _CombinedOutcomes, 'Enrollment ID by AC Project'[pre_projectenrolmentid]),          
            USERELATIONSHIP ( 'Enrollment ID by AC Project'[pre_enddate], 'Calendar'[Date] )
        )
BINewbie1's avatar
BINewbie1
Icon for Helper II rankHelper II
2 years ago

Hi Anonymous,

 

Thanks for this, really helpful. I should have thought about UNION.

 

I tried the DAX use suggested but it come up with an error as follows:

 

It might be worth saying that the datatype of [new_projectenrolmentlookup] is Text, so the error might be because of the use of VALUES?

 

I also tried using the previous RETURN code I was using as follows which passes the syntax check, but doesn't seem to be worked as every enrollment comes up as closed successfully - which isn't correct - although I need to check the data to completely rule this out.

 

 

Appreciate any further thoughts.

 

Thanks,

 

Adam

 

 

 

 

BINewbie1's avatar
BINewbie1
Icon for Helper II rankHelper II
2 years ago

Anonymous  thanks for pointing me in the right direction regarding Union and Treatas. After some playing around I got the following code to work as needed:

 

Count of Enrollments Closed With a Successful Outcome =
   
VAR _FinancialOutcomes = SUMMARIZE(CALCULATETABLE('Financial Outcomes',
        'new_clientsurveys'[new_outcomesum_ac_financial_base] >= 1,
        NOT ISBLANK ( 'Financial Outcomes'[new_projectenrolmentlookup] ),
        REMOVEFILTERS ('Calendar')),
            [new_projectenrolmentlookup])

VAR _SuccessfullNonFinancialOutcome = SUMMARIZE(CALCULATETABLE('Non-Financial Outcomes',
        'Non-Financial Outcomes'[new_outcomesubtype_ac] <> "Case concluded unsuccessfully",
        'Non-Financial Outcomes'[new_outcomesubtype_ac] <> "Client ceased to give instructions",
        'Non-Financial Outcomes'[new_outcomesubtype_ac] <> "Unknown Outcome",
         NOT ISBLANK ( 'Non-Financial Outcomes'[new_projectenrolmentlookup] ),
        REMOVEFILTERS ('Calendar')),
    [new_projectenrolmentlookup])

VAR _CombinedOutcomes =
    UNION(
        _FinancialOutcomes,
        _SuccessfullNonFinancialOutcome)

RETURN
    CALCULATE (
        [Count of Enrollments Closed],
        TREATAS (
            _CombinedOutcomes, 'Enrollment ID by AC Project'[pre_projectenrolmentid]),          
        USERELATIONSHIP ( 'Enrollment ID by AC Project'[pre_enddate], 'Calendar'[Date] )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi BINewbie1 ,

     

    Thanks for your feedback.

     

    Best regards,

    Adamk Kong