CODEWITHSUNDEEP
countdistinctssasmdx
ilija veselica
ilija veselica

Reputation: 9574

MDX - distinct count

I was following this article:

http://msdn.microsoft.com/en-us/library/aa902637%28v=sql.80%29.aspx

and my query for distinct count looks like this:

Count(CrossJoin({[Measures].[Submission Count]}, [Submission].[PK Submission].Members), ExcludeEmpty)

it returns always 1 more than it should (for example it returns 27 instead of 26).

In the same article there is this query (which is suppose to solve this problem):

Count(CrossJoin( {[Sales]}, 
Descendants([Customers].CurrentMember, [Customer Names])),
 ExcludeEmpty)

But I can't get it to work. I've tried these two but second one always returns 1 or 0 while the first one doesn't work (error: I have to explicitly define a level):

Count(CrossJoin( {[Measures].[Submission Count]}, 
    Descendants([Submission].CurrentMember, [Submission].[PK Submission])),
     ExcludeEmpty)



Count(CrossJoin( {[Measures].[Submission Count]}, 
    Descendants([Submission].[PK Submission].CurrentMember, [Submission].[PK Submission])),
     ExcludeEmpty)

Any idea what am I doing wrong?

Thanks!

Upvotes: 1

Views: 2493

Answers (1)

gonsalu
gonsalu

Reputation: 3184

The reason the first query returns "1 more than it should" is because the [Submission].[PK Submission].Members tuple set also includes the All member.

If you refer to the [PK Submission] level instead of all the members of the [PK Submission] hierarchy, it doesn't include the All member.

So, the following returns what you're expecting:

Count( CrossJoin( { [Measures].[Submission Count] }
                , { [Submission].[PK Submission].[PK Submission] })
     , ExcludeEmpty)

Upvotes: 3

Related Questions