When I try something like the below for a distinct count, I always get 1
mor than I should. Is it because of the All member? How can I set this
right?

Count(CrossJoin({measures.[dummymeasure]},
descendants([Product].[Product Name].currentmember)),excludeempty)

tia

--
Message posted via http://www.sqlmonster.com

Try using:

Descendants([Product].[Product Name].CurrentMember,,LEAVES)


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

thank you! now it is alright.

One more question, I am using an iif to display null if value is zero -
that means i repeat the count exprssion twice. Any way I can avoid that?


iif(Count(CrossJoin({measures.[dummymeasure]},
descendants([Product].[Product Name].currentmember,, leaves)),excludeempty)
= 0,null, Count(CrossJoin({measures.[dummymeasure]},
descendants([Product].[Product Name].currentmember,, leaves)),excludeempty))

--
Message posted via http://www.sqlmonster.com

You can define a second calculated measure that returns the null - can't
think of how to do it in one expression:

'Count(CrossJoin({measures.[dummymeasure]},
descendants([Product].[Product Name].currentmember,,
leaves)),excludeempty)'

Member [Measures].[EmptyCount] as
'iif([Measures].[ProdCount] = 0,
Null, [Measures].[ProdCount])'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!