Help Optimizing DAX Measure

[Anonymous]

Hi all,

Here is a link to my PBIX File 
Memberships Sample File.pbix

I have created a complex DAX measure that runs on a filtered dataset but becomes unusable on the full dataset. The calculations are accurate, but I need to optimize it for performance.

The Problem

Here is the error I get when trying to run it on the full dataset:

However, when I use some filters, it works as intended:Summary view

In the screenshot above, the numbers on the side represent customer IDs. The numbers in the values section of the matrix indicate when a customer is counted in the ‘beginning balance’ or ‘new sale’, etc.

The matrix acts as a ledger table that tracks when a customer is active or inactive.
Begin Balance means that a customer is active as of the first of the month.
New Start means a customer became a new or returning member during that month.
Canceled and Expired indicate that a customer is no longer active within that month.
End balance means that the customer is active as of the end of the month.

The ledger works by the following formula
Begin Balance + New Start - Canceleld - Expired = End Balance.

Each measure is built similarly, so I will choose the Expired measure since it is the most complex.

Model and Sample Data:

Here is a screenshot of my model:

The relationships are as follows:

Branch : Service Agreement

Branch Sk 1:* Branch Sk

Agreement Event Type : Service Agreement

Agreement Event Type Sk 1:* Agreement Event Type Sk

Location : Service Agreement

Location Sk 1:* Location Sk

Calendar : Service Agreement (INACTIVE)

Calendar Date 1:* Agreement Start Date Local

Here is a sample of what the Service Agreement data looks like:

Service Agreement Sk	Service Agreement Id	Agreement Type Sk	Agreement Event Type Sk	Branch Sk	Location Sk	Agreement Start Date Local	Agreement End Date Local	Agreement Created Date Local	Agreement Expired Date	Agreement Expired Or Canceled Date
185854	206976	4	5	41	6940075	2/15/2019	2/14/2020	2/28/2019	5/14/2020	5/14/2020
168754	188500	4	5	41	6940075	1/25/2018	1/24/2019	2/1/2019	4/24/2019	4/24/2019
790236	819559	4	4	41	6940075	2/14/2022	4/28/2022	12/27/2021	7/27/2022	4/28/2022
328022	343504	4	5	41	6940075	2/15/2020	2/14/2021	1/2/2020	5/15/2021	5/15/2021
467267	482396	4	5	41	6940075	2/14/2021	2/14/2022	12/28/2020	5/15/2022	5/15/2022

DAX Code Summary

Here is a summary of the code. Thanks to Chat GPT 🙂

Variables Defined:

1. MaxDate: Calculates the maximum date in the Calendar table.

2. CurrentMonthStart: Calculates the start date of the current month based on MaxDate.

3. CurrentMonthEnd: Calculates the end date of the current month based on MaxDate.

4. AgreementsByCustomer: Creates a summarized table of the 'Service Agreement' table, removing any filters on the Calendar table.

5. FilteredAgreements: Adds a new column "AgreementType" to AgreementsByCustomer based on various conditions related to the agreement's start and end dates.

6. PreviousAgreements, CurrentAgreements, FutureAgreements: Filters FilteredAgreements based on the "AgreementType" to create subsets of agreements.

7. IterationTable: Adds two new columns to CurrentAgreements:

   • PreviousActiveAgreement: Checks if there are any previous agreements that are still active.
   • FutureActiveAgreement: Checks if there are any future agreements that will become active.

Main Calculation:

• Result: Counts the number of rows in a filtered version of IterationTable where both PreviousActiveAgreement and FutureActiveAgreement are 0, grouped by Location Sk.

What it Ultimately Does:

The measure calculates the number of service agreements that are considered "Expired" based on the following conditions:

• The agreement is current as of the latest date in the Calendar table.
• There are no previous agreements that are still active.
• There are no future agreements that will become active.

The result is a count of such "Expired" agreements, grouped by Location Sk.

Below is the code for the measure  # Expired

# Expired = VAR MaxDate = CALCULATE( MAX( Calendar[Calendar Date] ), ( Calendar ) )

VAR CurrentMonthStart = EOMONTH( MaxDate, -1 ) + 1

VAR CurrentMonthEnd = EOMONTH( MaxDate, 0 )

VAR AgreementsByCustomer =

    CALCULATETABLE(

        SUMMARIZE(

            'Service Agreement',

            'Service Agreement'[Location Sk],

            'Service Agreement'[Agreement Event Type Sk],

            'Service Agreement'[Agreement Start Date Local],

            'Service Agreement'[Agreement Expired Or Canceled Date]

        ),

        ALL( Calendar )

    )

VAR FilteredAgreements =

    ADDCOLUMNS(

        AgreementsByCustomer,

        "AgreementType",

        SWITCH(

            TRUE(),

            'Service Agreement'[Agreement Start Date Local] < CurrentMonthStart &&

            'Service Agreement'[Agreement Expired Or Canceled Date] < CurrentMonthStart, "Previous",

            'Service Agreement'[Agreement Expired Or Canceled Date] >= CurrentMonthStart &&

            'Service Agreement'[Agreement Expired Or Canceled Date] <= CurrentMonthEnd &&

            'Service Agreement'[Agreement Event Type Sk] = 5, "Current",

            'Service Agreement'[Agreement Expired Or Canceled Date] > CurrentMonthEnd, "Future",

            BLANK()

        )

    )

VAR PreviousAgreements = FILTER(FilteredAgreements, [AgreementType] = "Previous")

VAR CurrentAgreements = FILTER(FilteredAgreements, [AgreementType] = "Current")

VAR FutureAgreements = FILTER(FilteredAgreements, [AgreementType] = "Future")

    VAR IterationTable =

        ADDCOLUMNS(

            CurrentAgreements,

            "PreviousActiveAgreement",

                VAR CurrentAgreementExpired = 'Service Agreement'[Agreement Expired Or Canceled Date]

                VAR LocationSk = 'Service Agreement'[Location Sk]

                VAR PreviousActiveAgreements =

                    FILTER(

                        PreviousAgreements,

                        'Service Agreement'[Location Sk] = LocationSk

                            && 'Service Agreement'[Agreement Expired Or Canceled Date] >= CurrentAgreementExpired     --If the previous agreement end comes before or on the current agreement cancel                 --TEchnically this one expired and didn't cancel 2892329

                            

                    )

                RETURN

                    IF( ISEMPTY( PreviousActiveAgreements ), 0, 1 ),

             "FutureActiveAgreement",

                --VAR FutureAgreementStart = 'Service Agreement'[Future Agreement Start Date]

                VAR CurrentAgreementExpired = 'Service Agreement'[Agreement Expired Or Canceled Date]

                VAR LocationSk = 'Service Agreement'[Location Sk]

                VAR FutureActiveAgreement =

                    FILTER(

                        FutureAgreements, --CurrentAgreements

                        'Service Agreement'[Location Sk] = LocationSk

                            && IF( ISBLANK( 'Service Agreement'[Agreement Start Date Local] ), FALSE(), 'Service Agreement'[Agreement Start Date Local] < CurrentAgreementExpired )   --if the current agreement's future start date comes before the current agreement canceled date --Blank > 1/7/2022, IF TRUE, ROW DOESN'T GET FILTERED

                            

                    ) 

                RETURN

                    IF( ISEMPTY( FutureActiveAgreement ), 0, 1 )--, --If FutureActiveAgreement is populated, then that means there is a valid future start date and therefore a canceled event should not appear. if future start date is blank, then it will result in false.

        )

        

        

        

        

    VAR Result =

        COUNTROWS( 

            GROUPBY( FILTER( IterationTable, [PreviousActiveAgreement] = 0 && [FutureActiveAgreement] = 0 )

                , 'Service Agreement'[Location Sk]

            )

        )

        

    RETURN Result

[aj1973]

@Anonymous 

Your sample doesn't return any data nor the error message ! How the community is going to help you !

[Anonymous]

Hi @aj1973, yes that's because it's only using a small portion of the data. What I'm asking is to return the same results for the # Expired measure but in a more optimized way.