Export (0) Print
Expand All
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Collapse the table of content
Expand the table of content
Expand Minimize

Distinct (MDX)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Evaluates a specified set, removes duplicate tuples from the set, and returns the resulting set.

Distinct(Set_Expression)

Set_Expression

A valid Multidimensional Expressions (MDX) expression that returns a set.

If the Distinct function finds duplicate tuples in the specified set, the function keeps only the first instance of the duplicate tuple while leaving the order of the set intact.

The following example query shows how to use the Distinct function with a named set, as well as how to use it with the Count function to find the number of distinct tuples in a set:

WITH SET MySet AS

{[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[Australia],

[Customer].[Customer Geography].[Country].&[Canada],[Customer].[Customer Geography].[Country].&[France],

[Customer].[Customer Geography].[Country].&[United Kingdom],[Customer].[Customer Geography].[Country].&[United Kingdom]}

MEMBER MEASURES.SETCOUNT AS

COUNT(MySet)

MEMBER MEASURES.SETDISTINCTCOUNT AS

COUNT(DISTINCT(MySet))

SELECT {MEASURES.SETCOUNT, MEASURES.SETDISTINCTCOUNT} ON 0,

DISTINCT(MySet) ON 1

FROM [Adventure Works]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft