DistinctCount

Analysis Services

Analysis Services

DistinctCount

Returns the number of distinct, non-empty tuples in a set.

Syntax

DistinctCount(«Set»)

Remarks

The DistinctCount function is equivalent to Count(Distinct(«Set»), ExcludeEmpty). This function can be applied only on calculated measures, and can involve only the topmost level. For example, the following MDX query will fail, because the DistinctCount function is being applied to a non-measure member:

WITH MEMBER Gender.a as 'DistinctCount({[Product Name].Members})'
SELECT
   { Gender.a } ON COLUMNS,
   { Customers.Children } ON ROWS
FROM Sales
WHERE (Measures.[unit sales])

By using a calculated measure to replace the non-measure member in the previous example, the following MDX query example will work:

WITH MEMBER Measures.a as 'DistinctCount({[Product Name].Members})'
SELECT
   { Measures.a } ON COLUMNS,
   { Customers.Children } ON ROWS
FROM Sales
WHERE (Measures.[unit sales])
Example

The following example returns 2, assuming all three tuples resolve to non-empty cells:

DistinctCount({Time.[1995], Time.[1997], Time.[1995]})