Distinct count measures are fact-of-life for an SSAS developer. No matter how much we try to avoid them they are always asked for and we have to deal with them somehow. Another painful fact is, as you might already know, we cannot create multiple distinct count measures in the same measure group. So each distinct count measure has to sit in its own measure group which,IMO, does not look right when browsing the cube. In this post, I want to show a method of creating distinct count calculated measures which I found on Richard Lees blog here with slight modification.
Using Adventure Works, let’s say the end users want to know the distinct count of customers who have placed orders on the internet. I can add a calculation like this in the cube
CREATE MEMBER CURRENTCUBE.[Measures].[Unique Customers] AS COUNT(NONEMPTY([Customer].[Customer].[Customer].members, [Measures].[Internet Order Count])), VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Internet Orders' ;
This is all fine and dandy, however, as soon as I added any attribute from customer dimension on the rows or filters, the results were showing incorrect values i.e. the same count of customers was repeated for all records.
The solution is to count the number of unique customers in the context of current attributes of customer dimension. For examples sake, lets take Customer City attribute. I tweaked the calculation like below to count customers only in the context of current members in City attributes and it started working as expected when Customer City attribute is used in rows, columns or filters.
CREATE MEMBER CURRENTCUBE.[Measures].[Unique Customers] AS COUNT(NONEMPTY( CROSSJOIN( [Customer].[Customer].[Customer].members, [Customer].[City].CurrentMember ),[Measures].[Internet Order Count])), VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Internet Orders' ;
Of course, you will have to add all the dimension attributes in the CROSSJOIN but ultimately a calculated, though complex, distinct count measure is better than having a number of physical distinct count measures IMHO.