DISTINCTCOUNT with FILTERing -1 IDs

Question

Hi, Please bear with me as I am newbie in SSAS/MDX. I want DISTINCTCOUNT measure on ID column of my fact table but after filtering -1 i.e. unknown IDs. I like to slice/dice on it as well. Here are my tables

    DimStudent
----------------------------
    ID       Name
    -1       Unknown
    1        Joe
    2        Tim
    3        Paul
    4        Zina


    DimCourse
----------------------------
    ID        Name
    -1        Not Registered
    11        Maths
    22        Science
    33        Arts



FactStudent
---------------
DimStudentID  DimCourseID  YearID  Marks
-----------------------------------------------------------------
1                    11                   2009     89
1                    33                   2009      10
1                    33                   2010      90
1                    -1                   2009      0
2                    -1                   2009      0
3                    22                   2009      90
4                    -1                   2009      0

In the above example StudentID 1 has one course with -1 ID and he has repeated one course. If I do Course distinct count for each student it will give me 3 for studentid 1 and for student 2 and 4 it will give me 1. My expected result (without considering year) is:

Student Name Course Count

Joe 2 (instead of 3) Tim 0 (instead of 1) Paul 1 Zina 0 (instead of 1)

And if do just distinct count it should give me 3 instead of 4.

Thanks again for your help and it’s highly appreciated.

Regards

Answer 1

To obtain a distinct count of DimCourseID from FactStudent, excluding DimCourseID values of -1, run a query selecting a distinct count of DimCourseID from FactStudent where DimCourseID <> -1 - like so:

select count(distinct DimCourseID)
from   FactStudent
where  DimCourseID <> -1