Forum Discussion

ronaldbalza2023's avatar
ronaldbalza2023
Continued Contributor
4 years ago

DAX query optimization

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] )
    )

 

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.