DAX query optimization

[ronaldbalza2023]

Hi everyone, so I have this measure that calculates overdue jobs. I wanted to optimize it as when I have filter dates (year & months), it throws an error. Thanks in advance 🙂

Overdue Jobs =
VAR MaxDateInCurrentContext =
    CALCULATE ( MAX ( 'Date'[Date] ), ALL ( 'Date'[Date] ) )
VAR MinDateInDateTable =
    CALCULATE ( MIN ( 'Date'[Date] ), ALL ( 'Date' ) )
RETURN
    CALCULATE (
        COUNTROWS ( 'Jobs' ),
        FILTER (
            ALLEXCEPT (
                'Jobs',
                'Jobs'[Jobs Job ID],
                Jobs[Jobs Job Client Name],
                Jobs[Jobs Job Name],
                Jobs[Jobs Job State]
            ),
            [Jobs Job Start Date] >= MinDateInDateTable
                && [Jobs Job Start Date] <= MaxDateInCurrentContext
                && (
                    [Jobs Job Completed Date] > MaxDateInCurrentContext
                        || [Jobs Job Completed Date] = BLANK ()
                )
                && ( [Jobs Job Due Date] < MaxDateInCurrentContext )
        ),
        USERELATIONSHIP ( 'Date'[Date], Jobs[Jobs Job Start Date] ),
        USERELATIONSHIP ( 'Unique Staff'[StaffID], Jobs[Job Manager ID] )
    )

[lbendlin]

Change USERELATIONSHIP to TREATAS

Rewrite the FILTER part - it doesn't have to be that complicated inside a CALCULATE

Rearrange the filters in order of cardinality impact

In general you will want to install DAX Studio and then check the query plan that is generated by the measure. That will help you to identify which part needs tuning.