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

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)