SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus
Creating Distinct Count calculations in DAX is pretty easy when the target of the operation is a column in the “fact table” of a star schema. When you want to apply the DISTINCTCOUNT function to a dimension attribute (such as Customer Country, Product Model, Employee Department, and so on), you need to apply one of the techniques described in the Related Distinct Count pattern we published on www.daxpatterns.com.
Technically, this is an implementation of the many-to-many relationship calculation in DAX, but you can safely ignore the complexity behind the many-to-many patterns: just copy the DAX formula corresponding to the common scenarios:
- Distinct Count on Attribute in Star Schema
- Distinct Count on Attribute in Snowflake Schema
- Distinct Count on Slowly Changing Dimension (SCD) Type 2
We will continue publishing new patterns in the next weeks – let us know your feedback about the DAX Patterns website!
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
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.