“Distinctcount” in DAX and MDX

If you want to count distinct values in DAX you have at least two ways of doing this. You can use the distinctcount or you can use countrows with distinct.

A sample can be viewed below

EVALUATE
SUMMARIZE (
    'Product',
    "Number of products version 1", DISTINCTCOUNT ( 'Product'[Product Id] ),
    "Number of products version 2", COUNTROWS ( DISTINCT ( 'Product'[Product Id] ) )
)

In MDX the same thing can be done using this code

WITH MEMBER CountDistinct
AS COUNT(DISTINCT [Product].[Product Id].[Product Id])

SELECT {[Measures].[CountDistinct]} ON 0
FROM [Internet Operation]

You also have a distinctcount in MDX that can be used.

Advertisements