Hello,

I have a MDX statement for the AS2K Foodmart 2000 database which returns
strange results. I want to count the elements of a set of elements and show
it to the user. I modified my original example as much as possible to
reproduce the problem. The result is the MDX statement below - one would
assume that MDX would return 1 as [Customers].[*Count] because there is only
one element in the set - unfortunately it returns two ... I there anyone who
can explain this behavior?

Thanks a lot for any help
Thomas

MDX sample for AS2K Foodmart 2000 database:

with
member [Customers].[*Count] as 'Count({[Customers].[All
Customers].[USA].[CA]})'
member [Product].[<Multiple Selection>] as 'aggregate({[Product].[All
Products].[Drink], [Product].[All Products].[Food]})'
select
{[Customers].[All Customers].[USA].[CA],[Customers].[*Count]} on rows,
{[Gender].[All Gender]} on columns
from [Sales]
where [Product].[<Multiple Selection>]

its not a problem, because you have a multiple selection
In your case you select 2 products, so the "*count" is aggregate 2 times (so
1 + 1 = 2)
if you select 3 products you'll have 3 etc...

I think you want a distinct count measure?
I mean you want top count the unique number of customers with sales in the 2
product.

Lookat the MSDN web site and search for basket analysis & distinct count
measures.

"Thomas Rath (MIS)" <trath (AT) misag (DOT) com> wrote