Introduction to MDX - Professional SQL Server Analysis Services 2005 with MDX - page 129

cousin, DistinctCount. The Count function is used to count the number
of items in the collection of a specific object like Dimension, Tuples, Set,
or Level. The DistinctCount, on the other hand, takes a Set_Expression
as an argument and returns a number that indicates the number of dis-
tinct items in the Set_Expression, not the total count of all items. Here
are the function definitions for each:

Count ( Dimension | Tuples | Set| Level)
DistinctCount ( Set_Expression )

Please take a look at the following query:

WITH MEMBER Measures.CustomerCount AS DistinctCount (
Exists
([Customer].[Customer].MEMBERS,[Product].[Product
Line].Mountain,
"Internet Sales"))
SELECT Measures.CustomerCount ON COLUMNS
FROM [Adventure Works]

The DistinctCount function counts the number of distinct members in the who have
purchased products of the product line Mountain. If a customer has purchased a
product twice then the distinct count function will just count the customer once. The
MDX function Exists is introduced in Analysis Services 2005 and is used to filter
customers who have only purchased product line Mountain through the Internet.
You will learn the Exists function in Chapter 7 . The result of the Exists function is
a set of customers who have purchased product line Mountain. The result of the
above query is 9590.

Dimension Functions, Level Functions, and

Hierarchy Functions

Functions in these groups are typically used for navigation and manipulation. Here
is an example of just such a function, the "Level" function from the Level group:
SELECT [Date].[Calendar].[Calendar Quarter].[Q1 CY
2004].LEVEL ON COLUMNS
FROM [Adventure Works]