Hi,

I am getting #ERROR with this from Adventure Works. Can someone point
out the mistake please? Thanks in advance.

WITH MEMBER [Measures].[X] AS
COUNT(FILTER(DESCENDANTS([Customer].CURRENTMEMBER,[Customer].[Customer]),[M*easures].[Reseller

Order Quantity]>0))
SELECT [Measures].[X] ON COLUMNS,
DESCENDANTS([Customer].[All Customers], [Customer].[Customer
Geography].[Full Name], SELF_AND_BEFORE) ON ROWS
FROM [Adventure Works]
WHERE [Customer].[City].&[Atlanta]&[GA]

Your descendants function in the calculated measure does not specify
which hierarchy to use (If you take the descendants portion of this
function down onto the rows clause of the query you will get a more
descriptive error). I also found that the where clause was excluding all
the customer members, but you still get a result because the [Reseller
Order Quantity] is unrelated to the customer dimension, so I think the
"All Customers" member is always being returned.

The following MDX runs:

[Customer].CURRENTMEMBER,[Customer].[Customer]),[Measures].[Reseller
Order Quantity]>0))'
SELECT {[Measures].[X],Measures.[Reseller Order Quantity]} ON COLUMNS,
DESCENDANTS([Customer].[All Customers], [Customer].[Customer Geography].
[Full Name], SELF_AND_BEFORE) ON ROWS
--FILTER(DESCENDANTS([Customer].[Customer].CURRENTMEMBER,[Customer].
[Customer]),Measures.[Reseller Order Quantity]>0) ON ROWS
FROM [Adventure Works]
WHERE [Customer].[City].&[Atlanta]&[GA]

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1137932890.586472.128330 (AT) g47g2000cwa (DOT) googlegroups.com>,
rose.say (AT) gmail (DOT) com says...

I see where you are going with this, and I have tried with the
following:

WITH MEMBER [Measures].[X] AS
COUNT(FILTER(DESCENDANTS([Customer].[Customer].CURRENTMEMBER,
[Customer].[Customer]),
[Measures].[Internet Order Quantity]>0))
SELECT
{[Measures].[X], Measures.[Internet Order Quantity]} ON COLUMNS,
DESCENDANTS([Customer].[All Customers], 0, SELF_AND_AFTER)
ON ROWS
FROM [Adventure Works]
WHERE [Customer].[City].&[Camarillo]&[CA]

X Internet Order Count
All Customers 1 27,659
United States 1 9,567
California 1 5,466
Camarillo 1 2
93010 1 2
Drew Raji 1 1
Kellie J. Gomez 1 1

I figured that means Drew Raji and Kellie Gomez are the only 2
customers from Camarillo that have placed an internet order before (one
order each in fact).

I am confused why [Measures].[X] is showing 1 for all the rows. I
expected X to be the "count of unique customers that have placed an
internet order". X=1 for the Customer Name level makes sense, but how
do I get the aggregated count up the hierarchy?
That means 93010 (the Postal Code) and Camarillo (the City) should be
showing 2 for X as there are 2 such internet customers in that city,
and even more for the State and Country level.

Sorry, my mistake, I did not double check the meta data and thought
[Customer].[Customer] was the hierarchy name, but [Customer].[Customer
Geography] is the hierarchy.

[Customer].[Customer] is an attribute, when we are counting the
descendants of [Customer].[Customer], what we are counting is the
Customer attribute associated with the current member (which is a one to
one relationship, hence X=1). The Customer attribute of the customer
dimension is not a part of any hierarchy so it does not have any
descendants. A simple change to make the X measure count the descendants
of the [Customer Geography] hierarchy down to the [Full Name] level
fixes this measure.

The following query should work.


WITH MEMBER [Measures].[X] AS
COUNT(FILTER(DESCENDANTS([Customer].[Customer Geography].CURRENTMEMBER,
[Customer].[Customer Geography].[Full Name]),
[Measures].[Internet Order Quantity]>0))
SELECT
{[Measures].[X], Measures.[Internet Order Quantity]} ON COLUMNS,
DESCENDANTS([Customer].[All Customers], 0, SELF_AND_AFTER)
ON ROWS
FROM [Adventure Works]
WHERE [Customer].[City].&[Camarillo]&[CA]


It produces the following results on my Laptop:

X Internet Order Quantity
All Customers 18484 60,398
United States 7819 21,344
California 4444 12,248
Camarillo 2 4
93010 2 4
Drew Raji 1 2
Kellie J. Gomez 1 2

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1138028558.824652.181360 (AT) g44g2000cwa (DOT) googlegroups.com>,
rose.say (AT) gmail (DOT) com says...

Hi,

Would it help your?

WITH
MEMBER [Measures].[X] AS
COUNT(FILTER(DESCENDANTS([Customer].[Customer
Geography].CURRENTMEMBER,[Customer].[Customer Geography].[Full Name]),
[Measures].[Internet Order Quantity]>0))
SELECT
{[Measures].[X], Measures.[Internet Order Quantity]} ON COLUMNS,
DESCENDANTS([Customer].[All Customers], 0, SELF_AND_AFTER)
ON ROWS
FROM [Adventure Works]
WHERE [Customer].[City].&[Camarillo]&[CA]


Vladimir Chtepa

"Rose" <rose.say (AT) gmail (DOT) com> schrieb im Newsbeitrag
news:1138028558.824652.181360 (AT) g44g2000cwa (DOT) googlegroups.com...