Forum Discussion

KyleFerrero's avatar
KyleFerrero
Frequent Visitor
1 year ago
Solved

Optimizing a Slow AverageX Measure

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 wonder...
  • KyleFerrero's avatar
    KyleFerrero
    1 year ago

    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.
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    KyleFerrero
    Frequent Visitor
    1 year ago

    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.