THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
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 9, 2012 11:55 PM by Marco Russo (SQLBI)



Layinka said:

Yeah, that makes a lot of sense

June 12, 2013 7:00 AM

dax newbie said:


Does the following measure make sense ?

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


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:

December 10, 2013 10:48 AM
New Comments to this post are disabled

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



Privacy Statement