The query has exceeded available resources, optimization needed

[alks_skla_f]

Hello, The point of my calculation is to count hours between case created date and case closed date with excluded weekends and bank holidays based on region.

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

-- Filter the TimeDim table for dates between StartDate and EndDate
VAR FilteredDates =
    FILTER(
        'TimeDim',
        'TimeDim'[Date] >= DATEVALUE(StartDate) &&
        'TimeDim'[Date] <= DATEVALUE(EndDate) &&
        //TimeDim[calculatedcolumn_test_ApacIsWorking]
        SWITCH(
            --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
        )
    )

-- Calculate total hours
VAR TotalHours =
    SUMX(
        --For each date in filtered table
        FilteredDates,
        VAR CurrentDate = 'TimeDim'[Date]
        RETURN
            // First day: Calculate hours from StartDate
            IF(
                CurrentDate = DATEVALUE(StartDate),
                24 - HOUR(StartDate), -- Remaining hours in the day
                // Last day: Calculate hours up to EndDate
                IF(
                    CurrentDate = DATEVALUE(EndDate),
                    HOUR(EndDate), -- Hours up to the end time
                    24 -- Full day (24 hours) for other dates
                )
            )
    )

RETURN TotalHours

However, when I am trying add this measure to table visual, I am getting this issue: The query has exceeded available resources.

How I can optimize my code?

[v-venuppu]

Hi @alks_skla_f ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @alks_skla_f ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @wardy912 for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[wardy912 · Accepted solution]

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!