Hi,

I am using AS2005 and trying to get a distinct count using MDX where
the sales is greater than 0. The Sales measure is Calc'd from 2
different measure groups (2 fact tables). It's quite easy to do using
this example which i ripped from another post.

WITH MEMBER
[MEASURES].[CalcCount]
AS
'COUNT(FILTER(CROSSJOIN({[Measures].[GrossPot]}, DESCENDANTS
([Outlet].[by Territory].CURRENTMEMBER, [Outlet].[by
Territory].[Outlet])),[Measures].[GrossPot]>0))'
SELECT
{[Measures].[GrossPot], [MEASURES].[CalcCount] }
ON COLUMNS,
[Outlet].[by Territory].CHILDREN ON ROWS
FROM
[MyCube]

This works great, BUT the problem is that I have multiple hierarchies
in the Outlet dimension (3 in total), so if i switch the Rows to
"[Outlet].[by Channel].CHILDREN ON ROWS" the result for each Channel
child is simply the total count of outlets that have GrossPot > 0.

I'm not sure if there is an example of this in the Adventure works.

I hope this is clear enough so that someone can help me.

Thanks,
Ben

why do you do a crossjoin between the measure and the dimension?

maybe this formula could help you:
COUNT(
FILTER (
exists(
DESCENDANTS(
[Outlet].[by Territory].CURRENTMEMBER,
[Outlet].[by Territory].[Outlet]
)
)
,[Measures].[GrossPot]>0
)
)

if the non empty behavior of the GrossPot meassure is setup correctly, maybe
this formula could be better:
COUNT(
exists(
DESCENDANTS(
[Outlet].[by Territory].CURRENTMEMBER,
[Outlet].[by Territory].[Outlet]
)
, {[Measures].[GrossPot]}
)
)

The exists keyword improve the performance by removing non existing members
from the calculation.


<benaud (AT) gmail (DOT) com> wrote

Hi Jeje,

Thanks for your answer, but your MDX gives me the exact same answers as
before (albeit better performance most likely), but when i change to
the alternate hierarchy on the rows, the answer is incorrect... for
example

Outlet by Territory (Which gives the correct answer)
101 20
102 25
103 30

Outlet by Channel (Which totals the above and displays for each
measure)
Department Stores 75
Newsagents 75
Discount Stores 75

There must be a way to get this to work across hierarchies.

try to count the members of your Key attribute, not the leaf level of a
hierarchy
If your key attribute is "Store" use this:
<dimenion>.<attribute>.<attribute>.members
instead of
<dimenion>.<hierarchy>.<attribute>.members

also, add the existing keyword:
exists(existing(<dimenion>.<attribute>.<attribute> .members))

Existing return only members used in the dimension regarding other
attributes from the same dimension; Exists return only the members used in
the context of the cube...
when you select a channel, this formula should filter only the stores into
this channel.

Using only Exists is not enough using only Existing is not enough too


<benaud (AT) gmail (DOT) com> wrote

Jeje,

This worked exactly right! But alas it's too slow once the cube is
fully loaded with data. Maybe once the aggegrations are setup i will
try it again.

Thanks for your help regardless. It has helped with my understanding
of MDX.