There are many ways to write DAX code to implement measures in the tabular model but writing improper DAX code may slow down the performance of the tabular model. I have encountered this problem during a recent tabular project implementation where, when you use conditional checking commands in DAX against a large number of rows, it may cause performance issues.
Requirement
The requirement was to create a measure for 3-month rolling average (Jan, Feb & March 2017) in a tabular model without using conditional checking methods (switch & if statements).
The sample data looks like this:
The table below contains sales values for three consecutive months: Jan, Feb & March 2017.
In this example, we are adding the totals of Jan, Feb & March 2018 and averaging it. The DAX code can be easily generated using conditional checking methods, but the “CPU Cost” will be high and it will directly affect the performance of the tabular model.
Alternative method:
In DAX we can use variables to store the values and reuse them for calculations as in any other programming language.
DAX code:
Author:Muditha Pelpola Consultant – Project Delivery
Muditha has more than 6 years’ experience in the software industry including development of data warehouses, reporting and analytics solutions in the Apparel, FMCG, and Healthcare domains.
