Optimize big measure

[alks_skla_f]

Hello, I am trying to calculate how many business hours case was opened (excluded weekends and bank holidays)
I wrote a measure:

measure_FirstResolutionHours = 
VAR StartDate = [measure_StartDate]
VAR EndDate = [measure_EndDate]

-- Filter the TimeDim table for dates between StartDate and EndDate
VAR FilteredDates =
    FILTER(
        'TimeDim',
         DATEVALUE('TimeDim'[Date]) >= DATEVALUE(StartDate) &&
         DATEVALUE('TimeDim'[Date]) <= DATEVALUE(EndDate) &&
        //TimeDim[calculatedcolumn_test_ApacIsWorking]
        SWITCH(
            TRUE(),
            --If user chooses APAC, then filter by APAC work days
            SELECTEDVALUE(cps_sfdc_cases_history[region]) = "APAC", TimeDim[APAC Working Day] = "1",
            --If user chooses NALA, then filter by NALA work days
            SELECTEDVALUE(cps_sfdc_cases_history[region]) = "NALA", TimeDim[EMEA Working Day] = "1"
        --'TimeDim'[APAC Working Day] = "1" -- Only include working days
        )
    )

VAR StartWorkingHours = [measure_StartWorkingHours]
VAR EndWorkingHours = [measure_EndWorkingHours]

-- Calculate total seconds
VAR TotalSeconds =
    SUMX(
        -- For each date in the filtered table
        FilteredDates,
        VAR CurrentDate = 'TimeDim'[Date]
        VAR WorkSecondsPerDay =
            SWITCH(
                TRUE(),
                -- Case 1: Start and End are on the same day
                DATEVALUE(StartDate) = DATEVALUE(EndDate) && CurrentDate = DATEVALUE(StartDate),
                    MAX(0, MIN(
                        8, (MIN(EndWorkingHours, TIMEVALUE(EndDate)) - MAX(StartWorkingHours, TIMEVALUE(StartDate))) * 24 -- Difference in hours between StartDate and EndDate
                    )) * 3600, -- Clamp to 8 hours and convert to seconds

                -- Case 2: Current date is the start date
                CurrentDate = DATEVALUE(StartDate),
                    MAX(0, MIN(
                        8, 
                        (EndWorkingHours - TIMEVALUE(StartDate)) * 24 -- Remaining hours from start time to 17:00
                    )) * 3600, -- Clamp to 8 hours and convert to seconds

                -- Case 3: Current date is the end date
                CurrentDate = DATEVALUE(EndDate),
                    MAX(0, MIN(
                        8, 
                        (TIMEVALUE(EndDate) - StartWorkingHours) * 24 -- Hours from 9:00 to the end time
                    )) * 3600, -- Clamp to 8 hours and convert to seconds

                -- Case 4: All other dates (full workday)
                8 * 3600 -- Full 8-hour workday in seconds
            )
        RETURN
            WorkSecondsPerDay
    )

RETURN (DIVIDE(TotalSeconds, 3600))/8

How can I optimize this measure, please?

[DataNinja777 · Accepted solution]

Hi @alks_skla_f ,

The original measure is slow because it uses sumx to iterate through every single date between the Start and End dates. If a case is open for 100 days, the engine has to run the logic 100 times. 

You can optimize your measure like below using calculate:

measure_FirstResolutionHours_Optimized = 
VAR StartDate = [measure_StartDate]
VAR EndDate = [measure_EndDate]
VAR StartTime = TIMEVALUE(StartDate)
VAR EndTime = TIMEVALUE(EndDate)

-- Define Working Hours
VAR StartWorkHour = [measure_StartWorkingHours] 
VAR EndWorkHour = [measure_EndWorkingHours]     
VAR SecondsPerDay = (EndWorkHour - StartWorkHour) * 24 * 3600

-- Determine Region Logic
VAR IsAPAC = SELECTEDVALUE(cps_sfdc_cases_history[region]) = "APAC"
VAR IsNALA = SELECTEDVALUE(cps_sfdc_cases_history[region]) = "NALA"

-- 1. Calculate Total Working Days
-- This replaces SUMX. We count rows in the date table efficiently.
VAR WorkingDaysCount = 
    CALCULATE(
        COUNTROWS('TimeDim'),
        DATESBETWEEN('TimeDim'[Date], DATEVALUE(StartDate), DATEVALUE(EndDate)),
        KEEPFILTERS(
            SWITCH(
                TRUE(),
                IsAPAC, 'TimeDim'[APAC Working Day] = "1",
                IsNALA, 'TimeDim'[EMEA Working Day] = "1", 
                'TimeDim'[EMEA Working Day] = "1"
            )
        )
    )

-- 2. Check if specific Start/End dates are working days
-- This prevents subtracting time if the case started on a Sunday or Holiday
VAR StartDate_APAC_Flag = LOOKUPVALUE('TimeDim'[APAC Working Day], 'TimeDim'[Date], DATEVALUE(StartDate))
VAR StartDate_EMEA_Flag = LOOKUPVALUE('TimeDim'[EMEA Working Day], 'TimeDim'[Date], DATEVALUE(StartDate))
VAR EndDate_APAC_Flag   = LOOKUPVALUE('TimeDim'[APAC Working Day], 'TimeDim'[Date], DATEVALUE(EndDate))
VAR EndDate_EMEA_Flag   = LOOKUPVALUE('TimeDim'[EMEA Working Day], 'TimeDim'[Date], DATEVALUE(EndDate))

VAR IsStartDateWorking = 
    SWITCH(
        TRUE(),
        IsAPAC, StartDate_APAC_Flag = "1",
        IsNALA, StartDate_EMEA_Flag = "1",
        StartDate_EMEA_Flag = "1"
    )

VAR IsEndDateWorking = 
    SWITCH(
        TRUE(),
        IsAPAC, EndDate_APAC_Flag = "1",
        IsNALA, EndDate_EMEA_Flag = "1",
        EndDate_EMEA_Flag = "1"
    )

-- 3. Calculate Adjustments 
-- Only subtract "missed" morning hours if the start day was a working day
VAR StartAdjustment = 
    IF(
        IsStartDateWorking, 
        MAX(0, (StartTime - StartWorkHour) * 24 * 3600), 
        0
    )

-- Only subtract "missed" evening hours if the end day was a working day
VAR EndAdjustment = 
    IF(
        IsEndDateWorking, 
        MAX(0, (EndWorkHour - EndTime) * 24 * 3600),
        0
    )

-- 4. Final Calculation
VAR TotalSeconds = 
    IF(
        -- Edge Case: Start and End are on the same day
        DATEVALUE(StartDate) = DATEVALUE(EndDate),
        IF(
            IsStartDateWorking, -- Only count if it's a working day
            MAX(0, (MIN(EndWorkHour, EndTime) - MAX(StartWorkHour, StartTime)) * 24 * 3600),
            0 
        ),
        -- Standard Case: Multiple days
        -- Formula: (Total Days * 8h) - (Missed Morning Time) - (Missed Evening Time)
        (WorkingDaysCount * SecondsPerDay) - StartAdjustment - EndAdjustment
    )

RETURN 
    DIVIDE(TotalSeconds, 3600) / 8

The resultant output looks like below:

I am attaching an example pbix file for your reference.

(This answer was written with the help of Gemini.)

Best regards,

[alks_skla_f]

Thank you very much! It works very quickly 🙂

[amitchandak]

@alks_skla_f , One of the ways to use networkdays and multiply by 8 
Business day with and without using DAX Function NETWORKDAYS: https://www.youtube.com/watch?v=Qs03ZZXXE_c
https://medium.com/@amitchandak/power-bi-dax-function-networkdays-5c8e4aca38c
Or refer 
https://exceleratorbi.com.au/calculating-business-hours-using-dax/