It is not uncommon for BI developers using DAX to create measures in their models to write complex formulas which sometimes impact on the performance of their reports/ Dashboards.
One way to minimize this performance issues is to wrap your formulas within variables.
Variables helps not only to reduce the complexity of your calculations but also more efficient and improve the performance of your DAX querries.
To illustrate: Imagine you want to calculate percentage increase in referral activities year on year
You may write a dax querry like this:
Referral Activity YOY % =
DIVIDE(
([Referral Activity] - CALCULATE([Referral Activity], SAMEPERIODLASTYEAR ( 'Activity dates'[Date]))),
CALCULATE([Referral Activity], SAMEPERIODLASTYEAR ( 'Activity dates'[Date]))
)
This calculation will produce the correct result. But there is an issue with it. Power BI DAX query engine processor will execute SAMEPERIODLASTYEAR DAX function twice which will increase the time it takes for query execution to complete.
How about modifying the above DAX query by using variable:
Referral Activity YOY % =
VAR Referral Activity LY =
CALCULATE([Referral Activity], SAMEPERIODLASTYEAR ( 'Activity dates'[Date]) )
RETURN
DIVIDE(([Referral Activity] - Referral Activity LY), Referral Activity LY)
This calculation generate result faster and more efficient. SAMEPERIODLASTYEAR DAX function is executed just once.
Comments