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

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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'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] )
            )