DAX Optimization - Business Hours Elapsed Calculated Column

Hi Rice 

Try this. It runs in well under a second for me in a test query.

 

BusinessHoursDurationTest-Owen =
VAR OneSecond = 1 / 3600 / 24
VAR StartDate =
    'SD - Incident Report Opened'[Date Link - Incident Submit Date]
VAR EndDate =
    'SD - Incident Report Opened'[Date Link - Support Centre Response Date]
VAR StartTime =
    'SD - Incident Report Opened'[Incident Submit Time]
VAR EndTime =
    'SD - Incident Report Opened'[Support Centre Response Time]
VAR StartDateTime = StartDate + StartTime
VAR EndDateTime = EndDate + EndTime
VAR DateRange =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Date',
            "Date", 'Date'[Date],
            "Weekday Number", 'Date'[Day of Week Sort] + 0
        ),
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
    )
VAR BusinessHoursFiltered =
    -- Customer filter applied by context transition
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Business Hours',
            "Weekday Number", 'Business Hours'[Weekday Number] + 0,
            "Business Hours Start", 'Business Hours'[Business Hours Start],
            "Business Hours End", 'Business Hours'[Business Hours End]
        )
    )
VAR DateBusinessHours =
    NATURALLEFTOUTERJOIN ( DateRange, BusinessHoursFiltered )
VAR BusinessHoursCalculation =
    SUMX (
        DateBusinessHours,
        VAR CurrentBusinessHoursStart = [Date] + [Business Hours Start]
        VAR CurrentBusinessHoursEnd = [Date] + [Business Hours End]
        VAR EffectiveStart =
            MAX ( StartDateTime, CurrentBusinessHoursStart )
        VAR EffectiveEnd =
            MIN ( EndDateTime, CurrentBusinessHoursEnd ) + OneSecond -- Align with original calc based on discrete seconds
        RETURN
            ROUND (
                MAX ( BLANK ( ), EffectiveEnd - EffectiveStart ) * 24,
                3
            )
    )
RETURN
    BusinessHoursCalculation

 

Notes:

• The basic idea is to determine the StartDateTime and EndDateTime at the start, then determine the portion of each segment of Business Hours that lies within that range.
• I used NATURALLEFTOUTERJOIN to join DateRange to BusinessHoursFiltered, then iterate over that table.
• Didn't use 'Ticket Time Table' to count seconds, but rather calculated difference between datetime values.
• I added a second to the end time to align with your original calculation.

Regards,

Owen