none
DistinctCount works correctly only on one Member in Dimension

    Question

  • Hello,

    I am trying to get two Distinct Counts where items have been completed. These are one for the Class and one for the Course. The Class rolls-up into Course, but I don't have that defined in the cube.

    I am using the following calculation for Class:
    DistinctCount( {[Dim Class].[Class Name].children} * [Measures].[Completed] )

    This works correctly. With the sample cube below, I would get a distinct count of 6. I can add other dimensions and there are no issues so far.

    I then use practicaly the identical calculation for a distinct count on the Course:
    DistinctCount( {[Dim Class].[Course Name].children} * [Measures].[Completed] )

    With this, I am getting wildly high numbers. I would expect the result to be about half because there are multiple classes for each course.It looks like it is counting all the Courses, not just the one(s) visible in the Browser in SSAS.

    Here is what a simplified version of the Fact and Dimension table look like:

    Student Fact Table
    EmployID  ClassKey    Completed   

    1              105                     1           
    2              110                     0
    2              120                     1              
    3              110                     0               
    4              110                     1               
    5              200                     1              
    6              210                     1               
    7              210                     1               
    8              220                     1               
    3              220                     1  

    Class Dimension Table
    ClassKey    ClassName        Course Name
    105            Stacks                Data Structures     
    110           Algorithms            Data Structures     
    120           Linked List            Data Structures     
    200           IE                       Browsers
    210           FireFox                Browsers
    220           Chrome               Browsers
    230           Opera                 Browsers

    Any idea what I am doing wrong? Thank you for the help.

    ~J

    p.s. If you need more info, I had posted a similair question over here: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/e6cd1582-d2f6-4a12-a924-4ac3839fc704#34565c28-ece5-42e4-98c3-cf8127363ea6

    Wednesday, February 22, 2012 8:42 PM

Answers

  • Vankayala's answer is correct, I just helped out with an optimized implementation.

    The list of Course members in SSAS would already be a distinct list, each course would only be represented once. So what you really wanted was to count a filtered list.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Jenna_Fire Monday, March 05, 2012 4:08 PM
    Saturday, March 03, 2012 11:19 AM
    Moderator

All replies

  • Hi Jenna,

    Could you explain how can we identify whether the class/course was completed? What AggregateFunction is used for the [Measures].[Completed]? In the example, for class 110 there are there students, 2,3 and 4. In them, 4 has completed but 2 and 3 did not have completed. So the class 110 would be identified as completed or not completed in your project?

    thanks,
    Jerry

    Monday, February 27, 2012 10:02 AM
    Moderator
  • Jerry,

    I really appreciate your response to my post.

    To identify if the class/course was completed you need to look in the Fact table. If the class/course was completed by a student, there is a 1, otherwise it is 0. The completion of the class/course is by individual not the class/course itself. There is no status that sets the class/course to completed. So even though three people took the class 110, only two people completed it. Right now, I can do a simple Sum and get the number of students who completed the class and/or course - that is working fine.

    To get the DistinctCount of class/course completions, I want to do something like this pseudo-code: If [Measures].[Completed] = 1 for one or more students, then Course/Class Completed = 1, else 0. In other words, if one or more students completed the class/course, mark it with a 1, otherwise mark it with a 0.

    At the end, I should have two measures: 1)the number of individuals who completed a class/course and 2)the number of classes/courses completed.  There are additional Dimensions that the class/course completions roll-up into (such as Service and Product), so having those calculations would make more sense that what I am showing here.

    I hope that clarifies it. I apologize that I wasn't clear in the original post.

    Thank you so much for your help with this.

    ~Jenna

    Monday, February 27, 2012 5:09 PM
  • 1) Number of students who completed course - It is not distinct count
              Just declare the measure as sum ... based on completed column. it should be good. 

             (which you said, already you have it working)

    2) I believe what you are looking is the courses that has completed students; if not apologies.

              Count( Filter(
              Descendants([Dim Class].CurrentMember, [Dim Class].[Course Name])
      ,  [Measures].[Completed] >= 1 )
               )


    Vankayala S

    • Proposed as answer by VankayS Friday, March 02, 2012 5:36 PM
    Friday, March 02, 2012 1:50 AM
  • Note that the Count( Filter(...)) pattern is very slow (see http://sqlblog.com/blogs/mosha/archive/2007/11/22/optimizing-count-filter-expressions-in-mdx.aspx)

    A better way of calculating this in MDX is:

    SUM(
              [Dim Class].[Course Name].[Course Name].Members ,
              IIF( [Measures].[Completed] >= 1 , 1, NULL)
           )


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by VankayS Friday, March 02, 2012 5:36 PM
    • Marked as answer by Jenna_Fire Friday, March 02, 2012 9:07 PM
    • Unmarked as answer by Jenna_Fire Friday, March 02, 2012 9:23 PM
    Friday, March 02, 2012 5:04 AM
    Moderator
  • I quickly did the query to say and give Jenna an idea that it is not distinctcount.

    Darren, Your query is better than mine.


     


    Vankayala S

    Friday, March 02, 2012 5:36 PM
  • Darren,

    I really appreciate your reply and I did mark this as an answer. However.  I was going over it again and I think this is close but not it yet.

    If I use the calculation as you wrote out, isn't it summing the number of courses that have been completed? What I am looking for is the unique number of courses that have been completed.

    Do I understand your calculation correctly?

    ~Jenna


    • Edited by Jenna_Fire Friday, March 02, 2012 9:28 PM tried the calculation again and the numbers weren't what I expected.
    Friday, March 02, 2012 9:06 PM
  • Vankayala,

    I'm not clear on this exactly... how come it is not a DistinctCount?

    Thank you.

    ~J

    Friday, March 02, 2012 9:29 PM
  • Vankayala's answer is correct, I just helped out with an optimized implementation.

    The list of Course members in SSAS would already be a distinct list, each course would only be represented once. So what you really wanted was to count a filtered list.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Jenna_Fire Monday, March 05, 2012 4:08 PM
    Saturday, March 03, 2012 11:19 AM
    Moderator
  • Darren,

    I didn't realize that. Again thanks so much for your time and patience. This has been a great help to me.

    ~Jenna

    Monday, March 05, 2012 4:09 PM