DAX query needs optimization

Hi mb769 ,
To optimize the code, I avoided using CROSSJOIN, which was generating unnecessary combinations of dates and tasks. Instead, I applied direct filters on the TASK and LEAVE tables, reducing the computational load. I also used variables to calculate values only once, preventing repeated calculations and improving efficiency. Finally, I simplified the use of SUMX and ADDCOLUMNS, reorganizing the functions to reduce processing time and make the code more straightforward and faster.

FTE = 
VAR _dateRange = FILTER(
    AcademicDate, 
    AcademicDate[Date] <= MAX(AcademicDate[Date]) && AcademicDate[Date] >= MIN(AcademicDate[Date])
)
VAR _tasksInDateRange = 
    FILTER(
        TASK,
        TASK[StartingDate] <= MAX(AcademicDate[Date]) && TASK[EndingDate] >= MIN(AcademicDate[Date])
    )
VAR _leavePeriods = 
    FILTER(
        LEAVE, 
        LEAVE[StartingDate] <= MAX(AcademicDate[Date]) && LEAVE[EndingDate] >= MIN(AcademicDate[Date])
    )
VAR _actualWorkedHours = 
    ADDCOLUMNS(
        _tasksInDateRange,
        "ActualWorkedHours",
        TASK[Numerator] - SUMX(
            FILTER(
                _leavePeriods,
                LEAVE[TASK_ID] = TASK[TASK_ID]
            ),
            LEAVE[Numerator]
        )
    )
VAR _includeLeave = 
    ADDCOLUMNS(
        _actualWorkedHours,
        "Include",
        CALCULATE(
            MAXX(
                _leavePeriods, 
                LEAVE[Include]
            )
        )
    )
RETURN 
    SUMX(
        _includeLeave,
        IF(
            OR([Include] = 1, ISBLANK([Include])), 
            [ActualWorkedHours], 
            0
        ) / TASK[Denominator]
    ) / (DATEDIFF(MIN(AcademicDate[Date]), MAX(AcademicDate[Date]), DAY) + 1)