locked
How to count distinct values from a single Measure field? RRS feed

  • Question

  • Below is a screenshot of my cube structure and the results I'm getting.  The results are perfect.  It's the next step that I'm struggling with.

    My measure is just a Count of rows.  In the browser results, I'm breaking it down by Test/Standard as the column and At Standard Label as rows.  Ultimately I'm trying to create a KPI that shows the % of tests passed.  The only way I can think to do this is to create some calculated fields: No Passed, No Failed, and Perc for Percent (not even sure if this last one is necessary).  The problem is that I have no clue how to filter out/sum the distinct values in the Measures table.  I have two possible fields that could be used:

    atStandard: Values in this field are a 1 (passed) and a 0 (failed)
    atStandardLabelID: Values in this field are the label IDs, actual values are 1 for "Needs Additional Practice" and 2 for "At Competency".   

    Seems like there should be an easy way to filter out these two distinct values and do a sum on them, but I'm still in the learning stages of my MDX and everything I've tried fuses at me: Use a tuple expression, sum function doesn't expect a string value -- I'm kind of stumped.


    Friday, July 6, 2012 8:20 PM

Answers

  • I figured it out -- I created two calculated members:

    [At Competency] - ([Proficiency Standards Labels].[At Standard Label].&[At Competency], [Measures].[At Standard Label ID])

    [Needs Additional Practice] - ([Proficiency Standards Labels].[At Standard Label].&[Needs Additional Practice], [Measures].[At Standard Label ID])

    Thanks everybody for taking the time to help and offer suggestions, always good to learn new things!!

    • Marked as answer by Elvis Long Thursday, July 12, 2012 10:42 AM
    Tuesday, July 10, 2012 3:21 PM

All replies

  • What you could do is create a new measure in your measure group and select create the measure as a distinct count.

    You can do it with the following below, but NOTE that there is a performance cost associated with creating a distinct count, because you have to create this in a new measure group. And also when you create a distinct count it is a lot harder to create aggregations on your cube because it cannot aggregate on all your dimensions because it has to be a distinct count. 

    But with that being said you can do the following:

    1. Open your Cube and make sure you are in the Cube structure
    2. In your above diagram where you see Measures, right click on Measures and select New Measure
    3. Where it says Usage click on the drop down and select Distinct Count.
    4. Now in your Source Column, select the column that you want the distinct count for.
    5. In your Example this will be atStandardLabelID
    6. Then click Ok.
    7. You will now see a new Measure Group with your New Distinct Measure.
    8. NOTE: You will have to process your cube to see your Distinct Count

    You can the repeat the steps for any other distinct count that you require.

    I hope that it helps.


    TEst

    • Proposed as answer by BKomm Monday, July 9, 2012 4:19 PM
    Monday, July 9, 2012 5:24 AM
  • Hi guavaq78

    Thanks for taking the time to reply!!  I'm out of the office today and will try following your instructions tomorrow...but I will say that I played around with the Distinct Count measure, and all it would do would show me is...well...the distinct counts :).  To be more specific, as I mentioned above, my measure for the atStandardLabelID will be one of two values.  Using the Distinct Count measure, depending on which attributes I use it shows the value for that measure as either being a "1" or a "2" and not the SUM of the distinct count, which is what I'm really needing.

    So if I have 50 of the first distinct value and 30 of the second distinct value, the 50 and 30 is what I'm needing to see, not the "1" and "2".

    But I don't remember exactly how I created the measure, so as I said I will go back and try following your instructions, hopefully that will do the trick!

    P.S. - Thanks for the warning about the performance hit.  It's a relatively small cube and didn't seem to slow performance down too much when I was playing with the Distinct Count measure, so I don't think that will be a problem.

    Monday, July 9, 2012 2:05 PM
  • Hi guavaq78

    Thanks for taking the time to reply!!  I'm out of the office today and will try following your instructions tomorrow...but I will say that I played around with the Distinct Count measure, and all it would do would show me is...well...the distinct counts :).  To be more specific, as I mentioned above, my measure for the atStandardLabelID will be one of two values.  Using the Distinct Count measure, depending on which attributes I use it shows the value for that measure as either being a "1" or a "2" and not the SUM of the distinct count, which is what I'm really needing.

    So if I have 50 of the first distinct value and 30 of the second distinct value, the 50 and 30 is what I'm needing to see, not the "1" and "2".

    But I don't remember exactly how I created the measure, so as I said I will go back and try following your instructions, hopefully that will do the trick!

    P.S. - Thanks for the warning about the performance hit.  It's a relatively small cube and didn't seem to slow performance down too much when I was playing with the Distinct Count measure, so I don't think that will be a problem.

    guavaq78 Solution should work in your scenario. Please try that and let us know your result.

    Please Help People When they need..!!

    Monday, July 9, 2012 4:20 PM
  • Below is a screenshot of what that gives me.  I'm not sure if it's what you were assuming...but what I'm needing are the numbers like what you see in my original screenshot above.  

    So to refresh what I'm going for -- I can get my numbers above simply by dragging the "At Standard Label ID" measure into the data region (usage is "count of rows") -- it will automatically show the number of rows for each label.  But I don't know how to store these values to use in a  KPI.  So when setting up a KPI, I need to create a percentage like: sum("Needs Additional Practice") / "Grand Total"

    I was thinking I could create Calculated Members that stores the sum of "At Competency" and "Needs Additional Practice" by using a mdx filter like sum(filter([Measures].[At Standard Label ID] = "1")) and sum(filter([Measures].[At Standard Label ID] = "0")), but everytime I true to create something I get fussed at for using tuples, or string members, or some other error that tells me I don't know what I'm doing :)

    Tuesday, July 10, 2012 12:18 PM
  • I figured it out -- I created two calculated members:

    [At Competency] - ([Proficiency Standards Labels].[At Standard Label].&[At Competency], [Measures].[At Standard Label ID])

    [Needs Additional Practice] - ([Proficiency Standards Labels].[At Standard Label].&[Needs Additional Practice], [Measures].[At Standard Label ID])

    Thanks everybody for taking the time to help and offer suggestions, always good to learn new things!!

    • Marked as answer by Elvis Long Thursday, July 12, 2012 10:42 AM
    Tuesday, July 10, 2012 3:21 PM