CODEWITHSUNDEEP
countdistinctmdxolap
ilija veselica
ilija veselica

Reputation: 9574

Count distinct in MDX (convert from SQL query)

SELECT COUNT (DISTINCT S.PK_Submission)
FROM Fact_Submission FS, Submission S
WHERE 
FS.FK_Submission = S.PK_Submission
AND FS.FK_Submission_Date >= 20100101
AND FS.FK_Submission_Date <= 20101231

I've tried this:

SELECT 
{[Measures].[Fact Submission Count]} ON AXIS(0), 
Distinct({[Submission].[PK Submission] }) ON AXIS(1)
FROM [Submission]
WHERE 
([Date].[Calendar Year].[2010])

but the result is the same

any idea how to write this in MDX? I'm pretty new at this so still haven't figured it out.

Upvotes: 0

Views: 8740

Answers (1)

ilija veselica
ilija veselica

Reputation: 9574

This is correct answer:

WITH SET MySet AS
{[Measures].[Fact Submission Count]} 
*
DISTINCT({ EXCEPT([Submission].[PK Submission].Members, [Submission].[PK Submission].[All]) })
MEMBER MEASURES.DistinctSubmissionCount AS
DISTINCTCOUNT(MySet)
SELECT {MEASURES.DistinctSubmissionCount} ON 0
FROM [Submission] 
WHERE 
([Date].[Calendar Year].[2010])

I have excluded "All" row because it's also being counted by COUNT function so I always had +1.

Upvotes: 2

Related Questions