locked
Distinct Count Blues RRS feed

  • Question

  • I really hate not knowing what I'm doing :(.  I've worked on this all day, Googled for answers, and I feel like a dog chasing his tail...

    I need to retrieve the # of distinct students that have received a suspension, cumulative for each school (Example, 30 suspensions at School A, but only 20 distinct students have been suspended).  Each suspension (along with other types of discipline) is recorded as an individual record in the Behavior Dimension (see my DSV below) with a Resolution Code of 'SSP3'.  The closest I can get is what you see below, I can sort out the suspension records and get a count of the number of times each student has been suspended, but when I try to condense this to get the distinct cumulative school count, I hit this circular error pattern of "Function is expecting a set or tuple for 1 argument and receiving a number or string" and when I fix it I get "set has different levels of dimensionality"....and on it goes.  I've tried using every variation of DistinctCount/existing/exists/NonEmpty/Filter that I can, and I just don't understand how this works well enough to make it work.

    So to sum, what I would need is a single column that represents the number of distinct students that have been suspended for each school, and it would be awesome if it filtered out the null values (school with 0 suspensions).

    Tuesday, January 15, 2013 9:40 PM

Answers

  • Hi,

    Not sure I can derive your cube dimension from this DSV, but assuming you have a Student dimension, School dimension, Behavior dimension and a count measure of behaviors, then the following mdx should help you out:

    WITH 
    MEMBER [Measures].[Suspended Students Count] AS 
    Iif
    (
    	IsEmpty([Measures].[Count of behaviors], [Behavior].[Resolution Code].&[SSP3]),
    	NULL,
    	Sum
    	(
    	   existing [Student].[Student].[Student], 
    	   Iif(IsEmpty([Measures].[Count of behaviors], [Behavior].[Resolution Code].&[SSP3]) ,NULL,1)
    	)
    )
    
    SELECT [Measures].[Suspended Students Count] ON 0
    , 
    NON EMPTY[School].[School].[School].MEMBERS ON 1
    FROM [Behavior]
    WHERE [Student].[School Year].&[2013]

    HTH,

    Hrvoje Piasevoli


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    • Marked as answer by puffster Wednesday, January 16, 2013 3:53 PM
    Tuesday, January 15, 2013 11:54 PM
  • This is absolutely perfect, thank you so much!  I'm pasting below what my final version looks like, and I'm hoping you'll share your knowledge a lil bit -- I'd love to learn how to fish, so to speak :).

    You're testing to see if this record is a suspension record.  If it's not, do nothing, if it is, you are summing the distinct student person ID values.  The only part I'm a little uncertain on, is how it is retrieving distinct student IDs...does the existing function retrieve distinct values by default?

    WITH 
    MEMBER [Measures].[Suspended Students Count] AS 
    Iif
    (
    	IsEmpty(([Measures].[SSP3], [Behavior].[Resolution Code].&[SSP3])),
    	NULL,
    	Sum
    	(
    	   existing [Behavior].[Person ID].[Person ID], 
    	   Iif(IsEmpty(([Measures].[SSP3], [Behavior].[Resolution Code].&[SSP3])) ,NULL,1)
    	)
    )
    
    SELECT [Measures].[Suspended Students Count] ON 0
    , 
    NON EMPTY [Location Alt].[Location Alias].children ON 1
    FROM [Behavior]
    WHERE [Student].[School Year].&[2013]

    • Marked as answer by Elvis Long Monday, January 21, 2013 8:57 AM
    Wednesday, January 16, 2013 4:02 PM

All replies

  • Hi,

    Not sure I can derive your cube dimension from this DSV, but assuming you have a Student dimension, School dimension, Behavior dimension and a count measure of behaviors, then the following mdx should help you out:

    WITH 
    MEMBER [Measures].[Suspended Students Count] AS 
    Iif
    (
    	IsEmpty([Measures].[Count of behaviors], [Behavior].[Resolution Code].&[SSP3]),
    	NULL,
    	Sum
    	(
    	   existing [Student].[Student].[Student], 
    	   Iif(IsEmpty([Measures].[Count of behaviors], [Behavior].[Resolution Code].&[SSP3]) ,NULL,1)
    	)
    )
    
    SELECT [Measures].[Suspended Students Count] ON 0
    , 
    NON EMPTY[School].[School].[School].MEMBERS ON 1
    FROM [Behavior]
    WHERE [Student].[School Year].&[2013]

    HTH,

    Hrvoje Piasevoli


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    • Marked as answer by puffster Wednesday, January 16, 2013 3:53 PM
    Tuesday, January 15, 2013 11:54 PM
  • This is absolutely perfect, thank you so much!  I'm pasting below what my final version looks like, and I'm hoping you'll share your knowledge a lil bit -- I'd love to learn how to fish, so to speak :).

    You're testing to see if this record is a suspension record.  If it's not, do nothing, if it is, you are summing the distinct student person ID values.  The only part I'm a little uncertain on, is how it is retrieving distinct student IDs...does the existing function retrieve distinct values by default?

    WITH 
    MEMBER [Measures].[Suspended Students Count] AS 
    Iif
    (
    	IsEmpty(([Measures].[SSP3], [Behavior].[Resolution Code].&[SSP3])),
    	NULL,
    	Sum
    	(
    	   existing [Behavior].[Person ID].[Person ID], 
    	   Iif(IsEmpty(([Measures].[SSP3], [Behavior].[Resolution Code].&[SSP3])) ,NULL,1)
    	)
    )
    
    SELECT [Measures].[Suspended Students Count] ON 0
    , 
    NON EMPTY [Location Alt].[Location Alias].children ON 1
    FROM [Behavior]
    WHERE [Student].[School Year].&[2013]

    • Marked as answer by Elvis Long Monday, January 21, 2013 8:57 AM
    Wednesday, January 16, 2013 4:02 PM