THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Calculate the rolling average for 12 months in #DAX and a nice IF optimization

Alberto published the Rolling 12 Months Average in DAX article on SQLBI a few days ago, which includes interesting consideration about how to avoid the pitfall of touching the boundaries of the Date table, which could result in a calculation error.

More interesting for the geek of us is the optimization of the measure to avoid the IF statement. As you may already know if you watched some of our last events or course, using IF statement in a measure might affect performance, especially (but not only) when you query a Tabular or Power Pivot model from MDX (i.e. from a PivotTable in Excel). In this article, instead of using:

Avg12M := IF ( [Sales] <> 0, <expression> )

the formula is

Avg12M := DIVIDE ( [Sales], [Sales] ) * <expression> )

as you can see, the DIVIDE has the only purpose to return 1 if the value to check is other than 0, and 0 if it is 0. The query plan generated by this expression is much faster than the IF one, and this technique can be used in many other similar scenarios.

Published Thursday, July 03, 2014 10:51 AM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

sled said:

'Am probably stupid, but i don't understand the formula and i don't see where is the Rolling average for 12 months compute ? Could you precise a little more with a specific example or something like that ? Thanks Marco.

A stupid guy :)

July 7, 2014 6:19 AM
 

Marco Russo (SQLBI) said:

It's in the <expression>, described in the article I was commenting (follow the link in the post) :-)

July 7, 2014 9:32 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement