Improve Performance - Measure DAX

Anonymous ,

Here's a version that does not use CALCULATE and should be MUCH faster. Have a good look at its mechanics and if it does not work on the first attempt, do not panic, just try to understand how it works and adjust it accordingly. But beware of CALCULATE that's executed row by row on a fact table. YOU SHOULD NEVER DO IT AS IT'LL ALWAYS KILL PERFORMANCE.

Total FTEs =
// Dates must NOT be connected to Facts
// and must be marked as a Date table
VAR __firstDate = MIN( 'Dates'[Date] )
VAR _lastDate = MAX( 'Dates'[Date] )
// assuming that WorkingDay is 1 for
// a working day and 0 for a weekend
var __workingDayCount = SUM( 'Dates'[WorkingDay] )
RETURN
if( __workingDayCount > 0,
    SUMX(
    
        FILTER(
            FACTS,
            // getting only the rows where
            // there is a non-empty overlap
            // between (start, end) and
            // (firstDate, lastDate)
            Facts[Start date] <= __lastDate
            &&
            Facts[Finish date] >= __firstDate
        ),
        
        // for each of the above rows calculate
        // the percentage of FTE
        var __fte = FACTS[Result gross (FTE)]
        var __lowerDate =
            MAX(
                __firstDate,
                Facts[Start date]
            )
        var __upperDate =
            MIN(
                __lastDate,
                Facts[Finish date]
            )            
        var __activityWorkingDayCount =
            SUMX(
                filter(
                    // We don't have to use
                    // ALL( Dates ) here due
                    // to the nature of the
                    // problem.
                    Dates,
                    and(
                        __lowerDate <= Dates[Date],
                        Dates[Date] <= __upperDate
                    )
                ),
                 Dates[WorkingDay]
            )
        return
            // do not use DIVIDE here as it
            // does nothing more than what
            // I've put in here and in fact
            // it slows down calculations
            __fte * __activityWorkingDayCount
                / __workingDayCount
    )
)