Measure Dimension

Hi

What's the difference between the two following queries as the returne value of the second one is "#Error".
My aim is to count the number of customers that purchased the mountain product through Internet.
I've used the Adventure Works sample database.

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS
DISTINCTCOUNT(EXISTS([Customer].[Customer].MEMBERS,
[Product].[Product Line].Mountain, "Internet Sales"))
SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS
FROM [Adventure Works]

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS
DISTINCTCOUNT(EXISTS([Customer].[Customer].MEMBERS,
[Product].[Product Line].Mountain, [Measures].[Internet Sales Amount]))
SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS
FROM [Adventure Works]

Thanks in advance,
Amir

Hi Amir,

There are two flavours of the Exists function and I think you're getting confused between them here. BOL does a good job of explaining what they do:

http://msdn2.microsoft.com/en-us/library/ms144936.aspx

As does Mosha:

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx
http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx
(these links were down at the time of writing though)

Your first query works because it is asking for all the Customers that had entries in the "Internet Sales" measure group associated with the Product Line 'Mountain'; that third parameter is the name of a measure group. Your second query fails because you're passing a measure, Internet Sales Amount, as the third parameter whereas the function is expecting a string.

Anyway, you perhaps want use the NonEmpty function instead which is more likely to give you what you want (Exists would return the Customers who had entries in the fact table even if the measure values for that row were null); you don't need to do a DistinctCount either, a Count is sufficient. Here's an example :

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS

COUNT(NONEMPTY([Customer].[Customer].MEMBERS,

([Product].[Product Line].Mountain,[Measures].[Internet Sales Amount] )))

SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS

FROM [Adventure Works]

HTH,

Chris

|||Chris,

Thanks for the reply. I appreciate it.
There is just one thing that I'm not sure of it in your sample using NONEMPTY. Shouldn't it be like this:

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS

COUNT(NONEMPTY(CrossJoin([Customer].[Customer].MEMBERS, ([Product].[Product Line].Mountain) ,[Measures].[Internet Sales Amount] ))

SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS

FROM [Adventure Works]

I mean without CrossJoin it is like a set of sets.

Thanks,
Amir|||OOPS,

This is the correct one:

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS

COUNT(NONEMPTY(CrossJoin([Customer].[Customer].MEMBERS, [Product].[Product Line].Mountain) ,[Measures].[Internet Sales Amount]

))

SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS

FROM [Adventure Works]|||

No, not necessarily, it's just a matter of style. Although your query returns the same results, you're counting a set of tuples consisting of combinations of Customer and Product, whereas my query is counting a set of Customers alone and finding the ones which aren't empty for Internet Sales Amount and Mountain.

Chris