MDX performance

Hi,
the topic of counting customers has been discussed several times in this forum but I have still a question. I want to filter customers that hav ea certain amount of sales per period. Instead of using a Count(Filter(customer) approach, I created a calculated member that "filters" a distinct count measure (Customer Key Distinct Count). For leaf members the count is multiplied with 1 or 0 depending on sales, for nodes a rollup takes place. The result is correct, but performance is poor. Is it possible to tune the expression?

CREATE MEMBER CURRENTCUBE.[MEASURES].Test
AS iif (count([Customer].[Customer Geography].currentmember.Children) = 0
-- leaf level
,
([Measures].[Customer Key Distinct Count])*
iif(([Measures].[Internet Sales-Sales Amount],[Customer].[Customer Geography].currentmember, [Date].[Calendar Time].currentmember)>3400,1,0)

--else (nodes)
,RollupChildren([Customer].[Customer Geography].currentmember,"+")

),

I'm using SSAS 2005
Thanks,

Marcus

Hi Marcus,

The expression above seems to be filtering leaf nodes of the [Customer Geography] hierarchy - if that's similar to Adventure Works, why is [Customer Key Distinct Count] evaluated for each customer (it should be 1 anyway)? Could something simpler serve your purpose, like:

With

Member [Measures].[Test] as

Count(Filter(existing [Customer].[Customer].[Customer]

, [Measures].[Internet Sales Amount] > 3400))

Select

{[Measures].[Test]} on 0

from [Adventure Works]

Test
3509

|||Hi Deepak,
thank you very much for spending your time on this question. Indeed I used the Count(Filter) approach first but it yielded a poor performance. So I tried it with a (Disitnct) Count Measure.

Cheers,
Marcus