Optimizing a Slow AverageX Measure

[KyleFerrero]

I have a measure that returns the average amount of days that an applicant is in the testing stage. I am relativly new to DAX and have rarely encountered performance issues in the past so I am wondering if there is room for optimization with this measure since it takes around 9 seconds to load on a card visual. My intended use for this is to put it on a visual along with a version of the measure that returns the same average bur from only the last 365 days. Having both of these measures on one visual greatly increases the load time, and I was wondering if I can modify my DAX to get this load time down. 

Here is the average measure:

Average Days In Testing =

CALCULATE(

    AVERAGEX(

        FILTER(

            ALL(Applicants),

            -- Filter applicants who have valid dates for the adjusted calculation

            NOT(ISBLANK([Testing Entry Date])) && NOT(ISBLANK([First Day not in Testing]))

        ),

        VAR APPLICANTID = Applicants[ApplicantID]

        VAR StartDate =

            CALCULATE(

                MIN(SnapshotApplicants[SnapshotDate]),

                REMOVEFILTERS(SnapshotApplicants),

                SnapshotApplicants[ApplicantsID] = APPLICANTID &&

                SnapshotApplicants[stage.id] = "1487532452977"

            )

        VAR ExitDateSetup =

            CALCULATE(

                MAX(SnapshotApplicants[SnapshotDate]),

                REMOVEFILTERS(SnapshotApplicants),

                SnapshotApplicants[ApplicantsID] = APPLICANTID &&

                SnapshotApplicants[stage.id] IN {"1487532452977", "1711544054156"}

            )

        VAR ExitDate = IF(ExitDateSetup = TODAY(), BLANK(), ExitDateSetup)

        VAR FirstDayNotInTesting =

            CALCULATE(

                MIN(SnapshotApplicants[SnapshotDate]),

                FILTER(

                    ALL(SnapshotApplicants),

                    SnapshotApplicants[ApplicantsID] = APPLICANTID &&

                    SnapshotApplicants[stage.id] <> "1487532452977" &&

                    SnapshotApplicants[SnapshotDate] > ExitDate

                )

            )

        VAR DaysInTesting =

            IF(

                NOT(ISBLANK(StartDate)) && NOT(ISBLANK(FirstDayNotInTesting)),

                CALCULATE(

                    COUNTROWS('Calendar'),

                    DATESBETWEEN('Calendar'[Date], StartDate, FirstDayNotInTesting - 1),

                    'Calendar'[IsWorkingDay] = TRUE

                ),

                BLANK()

            )

        RETURN DaysInTesting 

    )

)

Any optimization tips would be much appreciated, thanks!

[Anonymous]

Hi @KyleFerrero ,

Here are some optimisation tips that may help to reduce loading times:

1. Minimise the use of ALL and REMOVEFILTERS. These functions can be quite expensive as they remove all filters from a given column or table. Try to limit their use or replace them with more specific filters.

2. Break down complex calculations into simpler intermediate metrics. This helps Power BI to better optimise calculations. Also, use variables effectively. You are already using variables, which is great. Make sure variables are used to store values that are reused multiple times in the measure to avoid recalculations.

3. Filter early. Apply filters early in the calculation to reduce the number of rows processed.

4. Optimise date calculations. Date calculations can be particularly burdensome. Ensure that date-related calculations are as efficient as possible.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[KyleFerrero · Accepted solution]

Thank you for the tips, after using the performance analyzer on each of the different steps in my measure, I identifed that the First Day not in Testing was the major bottleneck. I was able to adjust this step of the measure by filtering the snapshots table first, and then using MINX on the filtered table to find the minimum date for the set of filter conditions:

First Day not in Testing =

VAR APPLICANTID = SELECTEDVALUE(Applicants[ApplicantID])

VAR ExitDate = [Testing Exit Date]

VAR FilteredSnapshots =

    FILTER(

        SnapshotApplicants,

        SnapshotApplicants[ApplicantsID] = APPLICANTID &&

        SnapshotApplicants[stage.id] <> "1487532452977" &&

        SnapshotApplicants[SnapshotDate] > ExitDate

    )

RETURN

IF(

    NOT(ISBLANK(ExitDate)),

    MINX(FilteredSnapshots, SnapshotApplicants[SnapshotDate])

)

This decreased the load time of my visuals greatly, and I was able to use this format for the other stages in my pipeline.

[KyleFerrero]

And here is the average measure for the last 365 days:

This Year Average Days In Testing =

CALCULATE(

    AVERAGEX(

        FILTER(

            ALL(Applicants),

            NOT(ISBLANK([Testing Entry Date])) && NOT(ISBLANK([First Day not in Testing])) &&

            [First Day not in Testing] >= TODAY() - 365

        ),

        VAR APPLICANTID = Applicants[ApplicantID]

VAR StartDate =

            CALCULATE(

                MIN(SnapshotApplicants[SnapshotDate]),

                REMOVEFILTERS(SnapshotApplicants),

                SnapshotApplicants[ApplicantsID] = APPLICANTID &&

                SnapshotApplicants[stage.id] = "1487532452977"

            )

        VAR ExitDateSetup =

            CALCULATE(

                MAX(SnapshotApplicants[SnapshotDate]),

                REMOVEFILTERS(SnapshotApplicants),

                SnapshotApplicants[ApplicantsID] = APPLICANTID &&

                SnapshotApplicants[stage.id] IN {"1487532452977", "1711544054156"}

            )

        VAR ExitDate = IF(ExitDateSetup = TODAY(), BLANK(), ExitDateSetup)

VAR FirstDayNotInTesting =

            CALCULATE(

                MIN(SnapshotApplicants[SnapshotDate]),

                FILTER(

                    ALL(SnapshotApplicants),

                    SnapshotApplicants[ApplicantsID] = APPLICANTID &&

                    SnapshotApplicants[stage.id] <> "1487532452977" &&

                    SnapshotApplicants[SnapshotDate] > ExitDate

                )

            )

        VAR DaysInTesting =

            IF(

                NOT(ISBLANK(StartDate)) && NOT(ISBLANK(FirstDayNotInTesting)) &&

                FirstDayNotInTesting >= TODAY() - 365, -- Additional filter condition here

                CALCULATE(

                    COUNTROWS('Calendar'),

                    DATESBETWEEN('Calendar'[Date], StartDate, FirstDayNotInTesting - 1),

                    'Calendar'[IsWorkingDay] = TRUE

                ),

                BLANK()

            )

        RETURN DaysInTesting

    )

)

[KyleFerrero]

Here are the related measures:

Testing Entry Date =

VAR APPLICANTID = SELECTEDVALUE(Applicants[ApplicantID])

RETURN

CALCULATE(

    MIN(SnapshotApplicants[SnapshotDate]),

    REMOVEFILTERS(SnapshotApplicants),

        SnapshotApplicants[ApplicantsID] = APPLICANTID &&

        SnapshotApplicants[stage.id] = "1487532452977")

First Day not in Testing =

VAR APPLICANTID = SELECTEDVALUE(Applicants[ApplicantID])

VAR ExitDate = [Testing Exit Date]

RETURN

CALCULATE(

    MIN(SnapshotApplicants[SnapshotDate]),

    FILTER(

    ALL(SnapshotApplicants),

        SnapshotApplicants[ApplicantsID] = APPLICANTID &&

        SnapshotApplicants[stage.id] <> "1487532452977" &&

        SnapshotApplicants[SnapshotDate] > ExitDate

)

)

Testing Exit Date =

VAR ExitDate =

    CALCULATE(

        MAX(SnapshotApplicants[SnapshotDate]),

        REMOVEFILTERS('SnapshotApplicants'),

        SnapshotApplicants[ApplicantsID] = SELECTEDVALUE(Applicants[ApplicantID]) &&

        SnapshotApplicants[stage.id] IN {"1487532452977", "1711544054156"}

    )

RETURN

IF(ExitDate = TODAY(), BLANK(), ExitDate)