Improve Performance - Measure DAX

[Anonymous]

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 days of the selected context)

I came up with a measure for FTE calculation but, as my FACTS table has 3,610,402 rows, I guess that is the reason why it is taking too long from a performance perspective. It is apparently working for 1 or 2 activities. But if I put is analysing the whole dataset, it gets too slow.

Do you know any way I can improve the performance of this measure?

Total FTEs = 
VAR _FirstDate =
    FIRSTDATE ( 'DateC'[Date] )
VAR _LastDate =
    LASTDATE ( 'DateC'[Date] )

RETURN
    SUMX(FACTS, 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
    ))

What would be expected, would be something like this, that works for a small number of activities but not for the whole dataset (I had the time horizon from 2020 to 2024 selected):

• Measure Total FTEs: in the table, as the context is 2020-2024, it calculates according to the 5 years duration;
• In the graph, it does the calculation according to the shown period, meaning that:
  • June 2020: 0.0716 FTEs;
  • July 2020: 0.0727 FTEs - takes into account the working days of one of the activities, increasing slighly
  • The other months follow a similar logic.
• What would also be a nice to have, but not a must have, is to have all the months showing into the X axis. I selected 2020-2024 as a page filter but the graphic shows only until Jan 2024 - the end date of the first activity

Thanks a lot!

Pedro

[AntrikshSharma]

@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 · Accepted solution]

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

[daxer-almighty]

By the way... @Anonymous, you are overusing and abusing CALCULATE. Please read at least some chapters from the ultimate book on DAX "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo. You'll thank me later 🙂

[Anonymous]

Hello @AntrikshSharma ,

First of all, thanks for your help, much appreciated!

I tried to use your approach and did the following:

• Created a YearMonth column (YYYYMM) in the DateC table
• Created a resumed version of the FACTS table

The results are not properly what I expected. And the calculated values also do not match what the previous preented values for each activity row.

The code was this one:

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

Also, tried to remove the SUM from the BetweenDates formula but an error showed up: "A single value for column 'Start date' in table 'FACTSShort' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I will try to think about other way for tackling this...

Thanks!

Edit: will try to create a pbix that mimics the data I have to put available for testing...