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

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