Investigate MDX DistinctCount discrepancy

Question

When I run this:

WITH MEMBER MEASURES.SETDISTINCTCOUNT AS  
DISTINCTCOUNT([Student Term].[Student ID].MEMBERS)
SELECT {MEASURES.SETDISTINCTCOUNT} ON 0  ,
[Student Term].[Term Code].&[1151] on 1
FROM [Enrollment]

I get a student count that agrees with the following sql (I'll refer to this correct count as "count A"):

SELECT COUNT(DISTINCT([student_id])) 
FROM  dbo.Fact_Enrollments 
WHERE Term_Code = '1151'

but unfortunately when I run this MDX (using a different but similar dimension) I get a different count that is less than "count A":

WITH MEMBER MEASURES.SETDISTINCTCOUNT AS  
DISTINCTCOUNT([Student Term].[Student ID].MEMBERS)
SELECT {MEASURES.SETDISTINCTCOUNT} ON 0  ,
[Term].[Term Type].[Academic Term].&[1151] ON 1
FROM [Enrollment]

---

I am not sure how to figure out what is going wrong in the second mdx query (more directly, what is going wrong in that "Term" dimesion). At first I thought maybe the Dim_Term table wasn't completely joining to the fact table (Fact_Enrollments) but this query which joins the two, does return "count A" (the correct count):

SELECT COUNT(DISTINCT([student_id]))
 FROM dbo.Fact_Enrollments
INNER JOIN dbo.Dim_Term ON Acad_Term_Cd=Term_Code
 WHERE Acad_Term_Cd = '1151'

I thought that maybe the best way to see what is going on is to find a list of all the distinct Student IDs that went into the first count and then do the same for the second count and take a deeper look at those in the first list but not the second but I do not know how to determine what the student IDs are that lead to the CountDistinct results that I am seeing.

I have tried a couple things beyond what I am writing here (left out because this is already pretty long) but I keep coming up with "count A" as my total result.

How can I find the list of distinct members that are counted by a DistinctCount call?

(or, alternatively what is the best way to discover the cause of this discrepancy)

Answer 1

I am pretty sure your relationships are messed up.

There is an important thing you should know. DISTINCTCOUNT is actually Count of distinct NON-EMPTY.

DistinctCount

Instead of the second query, try the below:

WITH MEMBER MEASURES.SETDISTINCTCOUNT AS  
COUNT(DISTINCT([Student Term].[Student ID].MEMBERS))
SELECT {MEASURES.SETDISTINCTCOUNT} ON 0  ,
[Term].[Term Type].[Academic Term].&[1151] ON 1
FROM [Enrollment]

This counts the empty cells too and thus should return a bigger value.

Answer 2

As a slight tweak to Sourav's script please add the EXISTING keyword:

WITH MEMBER MEASURES.SETDISTINCTCOUNT AS  
  COUNT(DISTINCT(EXISTING [Student Term].[Student ID].MEMBERS))
SELECT 
  {MEASURES.SETDISTINCTCOUNT} ON 0  ,
  [Term].[Term Type].[Academic Term].&[1151] ON 1
FROM [Enrollment];