none
Member function acting differently than other functions developed exactly the same RRS feed

  • Question

  • My query below has 4 calculated members, the first one does not work, the next three works perfectly.  As you can see, all four are identical with the exception of what they are filtering on.

    With 
    	member [0/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 0).count
    	member [1/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 1).count
    	member [2/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 2).count
    	member [3/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 3).count
    Select 
    	Non Empty 
    	{[0/3], [1/3], [2/3], [3/3]}
    		on columns,
    	Non Empty [Teacher].[Teacher Name].children on rows
    From	[Proficiency Standards Assessment - All]
    Where	(
    		 [Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		 [Proficiency Assessment Standards].[Diagnostic].&[False],
    		 [Location].[Location].&[Doss High]
    		)
    
    

    I'm expecting to see returned values of 5, 7, 9, & 2, respectively.  Instead I'm getting 71,159, 7, 9, & 2.

    The "At Standard" measure is defined as a smallint in the data warehouse it's getting pulled from, and the value will either be a 0 or 1 -- no null values.  It's being summed as it's aggregate measure function.  I'm guessing because it's summing a "zero" value, and the final sum is 0, that is why I'm getting whacked results, but not sure why it would matter.  Can anybody offer any suggestions?

    Tuesday, January 3, 2012 6:49 PM

Answers

  • This is seen when SSAS engine tries to compare cube value to numeric value meaning all nulls are converted to 0. Hence your count off shoots.

    Try first filtering non null students then applying Filter like:

     filter( NONEMPTY( [Student].[Student Name].children, [Measures].[At Standard]), [Measures].[At Standard] = 0).count
    


    http://dailyitsolutions.blogspot.com/
    • Marked as answer by puffster Thursday, January 5, 2012 2:46 PM
    Tuesday, January 3, 2012 10:23 PM

All replies

  • This is seen when SSAS engine tries to compare cube value to numeric value meaning all nulls are converted to 0. Hence your count off shoots.

    Try first filtering non null students then applying Filter like:

     filter( NONEMPTY( [Student].[Student Name].children, [Measures].[At Standard]), [Measures].[At Standard] = 0).count
    


    http://dailyitsolutions.blogspot.com/
    • Marked as answer by puffster Thursday, January 5, 2012 2:46 PM
    Tuesday, January 3, 2012 10:23 PM
  • You are like a genius or something!!  This worked perfectly, thanks.

    So is it just a good habit to get myself into, to use the NONEMPTY() function any time I'm doing a count, to safeguard against this?

    Thursday, January 5, 2012 2:48 PM