topic Re: DAX Query optimization in DAX Commands and Tips

DAX Query optimization

Anonymous — Mon, 14 Nov 2022 16:59:38 GMT

Hey,

Looking for some query optimization tips. I am having query as below, which is performing in around 3000ms (979 FE, 1766 SE).

How would you guys change this query in order to get better report response time :)?

MEASURE [%] =
    IF (
        (
            SUM ( Number )
                / ( DISTINCTCOUNT ( Date ) * 5 )
        ) > 1,
        1,
        SUM ( Number )
            / ( DISTINCTCOUNT ( Date ) * 5 )
    )

Thanks in advance!

Re: DAX Query optimization

AntrikshSharma — Mon, 14 Nov 2022 17:20:42 GMT

Anonymous Since the same code is used in 2 places you can store the code in a variable.

VAR Calc = DIVIDE ( [Measure], DISTINCTCOUNT ( Dates[Date] ) * 5 ) RETURN IF ( Calc > 1, 1, Calc )

Re: DAX Query optimization

CNENFRNL — Mon, 14 Nov 2022 19:33:12 GMT

Weird notations SUM( NUMBER ), DISTINCTCOUNT( Date ); whatever,

= MIN( 1, SUM ( Number ) / ( DISTINCTCOUNT ( Date ) * 5 ) )