A problem with the DistinctCount aggregate function for measures in Analysis Services 2000 is that it will not aggregate at leaf level if filtering only on a subset of the members in a level (or attribute). A customer or ours is all too familiar with this limitation. One of the enhancements to MDX in Analysis Services 2005 is that we can use sets in the where clause. This overcomes the problem as illustrated by the following screenshots.
Distinct count for all members of an attribute
Distinct count for just 2 years aggregated at leaf level (not the sum of distinct counts for each year)
Equivalent relational query
