Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Improve Performance - Measure DAX

Hello,   I opted to post this issue here has it can be viewed as a separate one. The original problem is here. (FTE calculation taking into account the working days of each activity and the working...
Need Help
Tips and Tricks
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago

    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
    )
)

     

     

AntrikshSharma's avatar
AntrikshSharma
Community Champion
5 years ago

Anonymous  of course that will be a slow code, you are doing CONTEXT TRANSITION 3.6 Million times over a fact table. There is not a lot or room for optimization, everything has to be written from scratch, can you provide PBI file? otherwise, do you have a year month column in your date table? if not create one and try to use this:

Total FTEs =
VAR _FirstDate =
    MIN ( 'DateC'[Date] )
VAR _LastDate =
    MAX ( 'DateC'[Date] )
RETURN
    SUMX (
        VALUES ( Dates[Year Month] ),
        CALCULATE (
            SUM ( FACTS[Result gross (FTE)] )
                * DIVIDE (
                    CALCULATE (
                        SUM ( 'DateC'[WorkingDay] ),
                        DATESBETWEEN (
                            'DateC'[Date],
                            MAX ( _FirstDate, SUM ( FACTS[Start date] ) ),
                            MIN ( _LastDate, SUM ( FACTS[Finish date] ) )
                        )
                    ),
                    CALCULATE (
                        SUM ( 'DateC'[WorkingDay] ),
                        DATESBETWEEN ( 'DateC'[Date], _FirstDate, _LastDate )
                    ),
                    0
                ),
            FACTS[Start date] <= _LastDate,
            FACTS[Finish date] >= _FirstDate
        )
    )

 Also, I don't get why are you summing dates. This MAX ( _FirstDate, SUM ( FACTS[Start date] ) ) part in your code doesn't make much sense. Try to debug and see what it returns and if it is not useful then remove it, because it is a part of DATESBETWEEN, i am sure summing dates isn't required.

daxer-almighty's avatar
daxer-almighty
Solution Sage
5 years ago

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
    )
)