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

Distinct Count of Customers in a SCD Type 2 in #DAX

If you have a Slowly Changing Dimension (SCD) Type 2 for your customer and you want to calculate the number of distinct customers that bought a product, you cannot use the simple formula:

Customers := DISTINCTCOUNT( FactTable[Customer Id] ) )

because it would return the number of distinct versions of customers. What you really want to do is to calculate the number of distinct application keys of the customers, that could be a lower number than the number you’ve got with the previous formula. Assuming that a Customer Code column in the Customers dimension contains the application key, you should use the following DAX formula:

Customers := COUNTROWS( SUMMARIZE( FactTable, Customers[Customer Code] ) )

Be careful: only the version above is really fast, because it is solved by xVelocity (formerly known as VertiPaq) engine. Other formulas involving nested calculations might be more complex and move computation to the formula engine, resulting in slower query.

This is absolutely an interesting pattern and I have to say it’s a killer feature. Try to do the same in Multidimensional…

Published Monday, April 09, 2012 11:55 PM by Marco Russo (SQLBI)

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

 

Layinka said:

Yeah, that makes a lot of sense

June 12, 2013 7:00 AM
 

dax newbie said:

Marco,

Does the following measure make sense ?

DistinctSCD2:=CALCULATE(DISTINCTCOUNT(Customers[Customer Code]), FactTable)

Thanks.

December 10, 2013 10:18 AM
 

Marco Russo (SQLBI) said:

Yes, it's the same as the second Customers measure in my blog post.

More info on the syntax you used are available here: http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering

December 10, 2013 10:48 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