Optimize big measure

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,