The query has exceeded available resources, optimization needed

Hi alks_skla_f 

Please see optimised code below:

measure_test_FirstResolutionHours =
VAR StartDate = SELECTEDVALUE(cps_sfdc_cases_history[CaseCreatedDate])
VAR EndDate = SELECTEDVALUE(cps_sfdc_cases_history[CaseHistoryCreatedDate])
VAR Region = SELECTEDVALUE(cps_sfdc_cases_history[region])

-- Handle invalid cases
IF(ISBLANK(StartDate) || ISBLANK(EndDate), BLANK(),

VAR StartDay = DATEVALUE(StartDate)
VAR EndDay = DATEVALUE(EndDate)

-- Count working days between Start and End
VAR WorkingDays =
    CALCULATE(
        COUNTROWS('TimeDim'),
        DATESBETWEEN('TimeDim'[Date], StartDay, EndDay),
        SWITCH(
            TRUE(),
            Region = "APAC", 'TimeDim'[APAC Working Day] = 1,
            Region = "NALA", 'TimeDim'[EMEA Working Day] = 1,
            FALSE
        )
    )

-- Calculate partial hours for first and last day
VAR FirstDayHours = 24 - HOUR(StartDate)
VAR LastDayHours = HOUR(EndDate)

-- Total hours = Full working days (minus first & last) + partials
RETURN
    IF(WorkingDays <= 1,
        LastDayHours - HOUR(StartDate),  -- Same day case
        (WorkingDays - 2) * 24 + FirstDayHours + LastDayHours
    )
)

Removed expensive FILTER + SUMX
Replaced with CALCULATE + COUNTROWS using DATESBETWEEN.

Region logic simplified with SWITCH
No nested FILTER inside measure.

Avoided iterating over each date
Compute working days count and adjust for first/last day hours.

Handled edge cases
If Start and End are on the same day, calculate difference directly.

Prevents resource overload
No large intermediate tables; uses scalar calculations.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!