Solved: DAX Query optimization

Anonymous · ‎11-14-2022

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!

AntrikshSharma · ‎11-14-2022

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

View solution in original post

CNENFRNL · ‎11-14-2022

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

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

AntrikshSharma · ‎11-14-2022

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

DAX Query optimization

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024