Using Multiple VARs as a Filter Context with an OR Condition

[BINewbie1]

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 - Financial and Non-Financial, the data for which are held in two other tables.

I've got the two filter conditions written as varibles, and they both work correctly independently (i.e. if I use either var on its own). I need either some way of merging the two varibles together in a single list (both VARs should just be a single column of the enrollment reference numbers in a table) or some DAX which enables them to operate together in an OR condition.

The Measure is currently written as follows - this works correctly but just counting enrollments that closed with a Financial Outomce:

Test Count of Enrollments Closed With a Successful Outcome =
VAR _FinancialOutcomes =
    SUMMARIZE (
        CALCULATETABLE ( 'Financial Outcomes', REMOVEFILTERS ( 'Calendar' ) ),
        [new_projectenrolmentlookup],
        "ID", [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",
            REMOVEFILTERS ( 'Calendar' )
        ),
        [new_projectenrolmentlookup],
        "ID", [new_projectenrolmentlookup]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Enrollment ID by AC Project'[pre_projectenrolmentid] ),
        _FinancialOutcomes,
        USERELATIONSHIP ( 'Enrollment ID by AC Project'[pre_enddate], 'Calendar'[Date] )
    )

I've tried using NATURALINNERJOIN to merge the tables but get an error about their not being a common column or its not in the same data format - I've checked and the ProjectEnrollmentlookup is a TEXT field across the data model. I've also tried using the or operator || in various formats, and can't seem to get that to work correctly.

Edit: A complicating factor is that I need to be able to break down the number of closed enrollments with a successful outcome, by two other fields:

1. the type of outcome that was achieved, in both outcomes table this in contained in a field called [new_outcomesubtype_ac].

2. the type of issue, which it a field called 'Enrollment ID by AC Project' [new_legalarea_ac_display], in the Enrollment table.

When using just one of the outcomes tables, as in the measure above, this (is it data liniage?) seems to be maintained, I need that to work with a measure that considers either outcomes tables, as expained above.

My Data Model Is a little complex and looks like this:

The relationships between the Enrollment Tables to the Outcomes tables flows through a survey table using Bi-Directional relationships. I'm interested in Closed Enrollments in a particular date period which requires use of an inactive relationship with the calendar table. Other than this measure it all seems to be working correctly as I need it to.

Grateful if anyone can offer some support helping to unpick the issues here.

Many thanks,

Adam

[Anonymous]

Hi @BINewbie1 ,

To address your issue of merging tables with NATURALINNERJOIN and handling the OR condition with multiple VARs in DAX, let's go step-by-step. Here's how you can approach it:

Step 1: Merge the VARs into a Single List
You need to create a single table that contains the combined list of enrollment references from both the financial and non-financial outcomes. You can achieve this using UNION.

Step 2: Use the Combined List in the Calculation
Once you have the combined list, use it as a filter context in your measure.

Try to modify your formula like below:

Test Count of Enrollments Closed With a Successful Outcome =
VAR _FinancialOutcomes =
    SUMMARIZE (
        CALCULATETABLE ( 'Financial Outcomes', 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",
            REMOVEFILTERS ( 'Calendar' )
        ),
        [new_projectenrolmentlookup]
    )
VAR _CombinedOutcomes = 
    UNION(
        _FinancialOutcomes,
        _SuccessfullNonFinancialOutcome
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Enrollment ID by AC Project'[pre_projectenrolmentid] ),
        TREATAS(VALUES(_CombinedOutcomes[new_projectenrolmentlookup]), 'Enrollment ID by AC Project'[pre_projectenrolmentid]),
        USERELATIONSHIP ( 'Enrollment ID by AC Project'[pre_enddate], 'Calendar'[Date] )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BINewbie1]

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 · Accepted solution]

@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]

Hi @BINewbie1 ,

Thanks for your feedback.

Best regards,

Adamk Kong