Forum Discussion

BINewbie1's avatar
BINewbie1
Helper 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] )
        )
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
    Helper 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
      Helper 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