Forum Discussion

mb769's avatar
mb769
Advocate I
1 year ago
Solved

DAX query needs optimization

The basic measure I use in my report, is the number of staff members (in a given acadmic year, selected with a slicer in my DAX date table 'AcademicDate'). This is simple, but I can only count the em...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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)
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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)
  • mb769's avatar
    mb769
    Advocate I
    1 year ago

    Wow, this certainly improves a lot!

    I never realised that I could perfectly omit the CROSSJOIN

    • mb769's avatar
      mb769
      Advocate I
      1 year ago

      Well, I spoke too soon. I cannot omit this CROSSJOIN.
      I need the combiniation between DATE and TAKS, because a LEAVE can be for only a small period of a TASK. The CROSSJOIN function allowed me to check on day-by-day basis whether I had to take a LEAVE into account.

      Without the CROSSJOIN, the LEAVE is substracted for the whole of the TASK.

      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super User
        1 year ago

        Hi mb769 ,

        I made some changes here to include the CROSSJOIN, please let me know if was optimized:

        FTE = 
VAR _dateRange = 
    FILTER(
        AcademicDate, 
        AcademicDate[Date] >= MIN(AcademicDate[Date]) &&
        AcademicDate[Date] <= MAX(AcademicDate[Date])
    )

VAR _taskRange =
    FILTER(
        TASK,
        TASK[StartingDate] <= MAX(AcademicDate[Date]) &&
        TASK[EndingDate] >= MIN(AcademicDate[Date])
    )

VAR _expandedTasks =
    FILTER(
        CROSSJOIN(_dateRange, _taskRange),
        AcademicDate[Date] >= TASK[StartingDate] &&
        AcademicDate[Date] <= TASK[EndingDate]
    )

VAR _leaveFiltered =
    FILTER(
        LEAVE, 
        LEAVE[StartingDate] <= MAX(AcademicDate[Date]) &&
        LEAVE[EndingDate] >= MIN(AcademicDate[Date])
    )

VAR _actualWorkedHours =
    ADDCOLUMNS(
        _expandedTasks,
        "ActualWorkedHours",
        TASK[Numerator] - SUMX(
            FILTER(
                _leaveFiltered,
                LEAVE[StartingDate] <= AcademicDate[Date] &&
                LEAVE[EndingDate] >= AcademicDate[Date] &&
                LEAVE[TASK_ID] = TASK[TASK_ID]
            ),
            LEAVE[Numerator]
        )
    )

VAR _includeLeave =
    ADDCOLUMNS(
        _actualWorkedHours,
        "Include",
        CALCULATE(
            MAXX(
                _leaveFiltered, 
                LEAVE[Include]
            ),
            FILTER(
                _leaveFiltered, 
                LEAVE[StartingDate] <= AcademicDate[Date] &&
                LEAVE[EndingDate] >= AcademicDate[Date] &&
                LEAVE[TASK_ID] = TASK[TASK_ID]
            )
        )
    )

RETURN 
    SUMX(
        _includeLeave,
        IF(
            OR([Include] = 1, ISBLANK([Include])), 
            [ActualWorkedHours], 
            0
        ) / TASK[Denominator]
    ) / (DATEDIFF(MIN(AcademicDate[Date]), MAX(AcademicDate[Date]), DAY) + 1)
    • Bibiano_Geraldo's avatar
      Bibiano_Geraldo
      Super User
      1 year ago

      Happy it was helpful.

       

      Thank you for your feedback.