Returns the distinct number of members in the set.
In the example, the two distinct members [Paris] and [Madrid].
Query
WITH
SET [Cities] AS {[Geography].[Geo].[Paris], [Geography].[Geo].[Madrid], [Geography].[Geo].[Paris]}
MEMBER [Measures].[M. Count] AS DistinctCount( [Cities] * [Measures].[Amount] )
SELECT
[Measures].[M. Count] ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
SET [Cities] AS {[Geography].[Geo].[Paris], [Geography].[Geo].[Madrid], [Geography].[Geo].[Paris]}
MEMBER [Measures].[M. Count] AS Count( Distinct([Cities]) * [Measures].[Amount], EXCLUDEEMPTY)
SELECT
[Measures].[M. Count] ON 0
FROM
[Sales]
The DistinctCount function evaluates the tuples of the set ignoring empty ones.
In the example, all cities without an [Amount] value are ignored.
Query
WITH
MEMBER [Measures].[M. Count] AS DistinctCount( [Measures].[Amount] * [Geography].[Geo].[City].Members )
SELECT
{ [Measures].[M. Count] } ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[M. Count] AS Count( Distinct( [Measures].[Amount] * [Geography].[Geo].[City].Members) , EXCLUDEEMPTY)
SELECT
{ [Measures].[M. Count] } ON 0
FROM
[Sales]
Like the Count function, DistinctCount uses the current member of the [Measures] when not specified.
Note that we are not using the [Measures] dimension for the calculated member using DistinctCount function.
Query
WITH
MEMBER [Measures].[the-answer] AS 42, SOLVE_ORDER = 0
MEMBER [Geography].[Geo].[City Count] AS DistinctCount( [Geography].[Geo].[City].Members ), SOLVE_ORDER = 10
SELECT
{ [Geography].[Geo].[City Count] } ON 0
FROM
[Sales]
WHERE
[Measures].[the-answer]
Result
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[City Count] AS Count( [Geography].[Geo].[City].Members )
SELECT
{ [Geography].[Geo].[City Count] } ON 0
FROM
[Sales]