DAX Calculation based on Previous Day and a Constant value

MK3010 · March 10, 2021, 4:25am

Hi Team,

I am trying to do DAX calculation based on below logic…

Storage/Day = #Total Storage Location from Previous Day + Count of Pallet-LPN - 116.

As I need to subtract a constant number it is challenging somehow I calculated and got the correct number for Date 16-Feb to 20-Feb but it is very slow in production. It takes around 1 mint for 2 months of data and my data keeps on growing …

I am attaching the sample PBIX file and below is the DAX I used to get the number. My actual data starts from 31-Dec-2020 which is just used to get the data from 1-Jan-2021 and it will be hidden from user.

Storage/Day = 
VAR currDate = SELECTEDVALUE('Date'[Date])
VAR prevDay =
    IF (
        MONTH ( SELECTEDVALUE ( 'Date'[Date] ) ) < MONTH ( currDate ),
        IF ( NOT ( ISINSCOPE ( 'Item'[Profit Center] ) ), 9000 ),
        CALCULATE ( [# Total Location_Storage], PREVIOUSDAY ( 'Date'[Date] ) )
    )
RETURN
    IF (
        NOT ( ISBLANK ( [# Total Location_Storage] ) ),
        IF (
            ISINSCOPE ( 'Item'[Profit Center] ),
            prevDay + [Sum of Pallet-LPN],
            prevDay + [Sum of Pallet-LPN] - 116
        )
    )

9000 - has been used to get the correct number for 31-Dec-2020 .

Sample DAX calculation PreviousDayAndConstant.pbix (761.3 KB)

AntrikshSharma · March 10, 2021, 10:55am

@MK3010 Replace DISTINCTCOUNT with SUMX ( DISTINCT ( ‘Storage’[Location] ), 1 ), now your visual runs in ~140 ms

compared to DISTINCTCOUNT:

The problem is that DISTINCTCOUNT is a non-additive function so for each change in filter context FE ask SE for a separate data cache, and even if each request is executed in 1ms then 1ms * 1475 would be just for DISTINCT Values, with SUMX FE simply ask everything at once and then does DISTINCTCOUNT

MK3010 · March 10, 2021, 4:22pm

Hi @AntrikshSharma,

Thankyou for your optimization eye… I had tried with …

COUNTROWS ( SUMMARIZE( ‘Storage’, ‘Storage’[Location] ) ) after reading it in another post but this didn’t improve performance much but now I understood.

And on the Storage/Day measure how do you deal with a constant value addition/subtraction. Here I tried checking for the total and then subtract the constant value…

Thanks
Mukesh

AntrikshSharma · March 11, 2021, 7:56am

@MK3010 Constants won’t impact the performance at all.

MK3010 · March 11, 2021, 8:00am

Hi @AntrikshSharma,

I meant, the way I have subtracted the constant from total is correct, mostly you always have better and optimized way of DAX which gives new way to write for future DAX…

Thanks
Mukesh

AntrikshSharma · March 11, 2021, 8:35am

@MK3010 All set, the only thing I would suggest is when you have to use the same measure in different branches of IF then store it in a variable outside IF, if a measure is only used once inside IF then always declare it inside IF

Read this: https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/

Here is an example:

With variables - 2 Storage Engine Queries:

Without variables - 3 Storage Engine Queries:

MK3010 · March 11, 2021, 8:58am

Hi @AntrikshSharma,

Thank you for your guidance, will follow your approach.

Thanks
Mukesh