Median without Median function

All Replies

• Thursday, February 14, 2013 10:11 AM

  Moderator

   

   
  

  0

  Hi QCCAT,

  How do you configure the relationship between the dimension [Breast Tumour Size] and the [Breast Tumour Size Median] measure group? Please refer to: http://technet.microsoft.com/en-us/library/ms166560.aspx

  Thanks,
  Eileen
  
  If you have any feedback on our support, please click here

  ---

  Eileen Zhao
  TechNet Community Support

  

  • Reply
  • Quote

   
• Thursday, February 14, 2013 11:03 PM

   

   
  

  0

  hi Eileen,

  [Breast Tumour Size Median] is a calculated measure based on a measure group called Breast Screening, which has a fact table row count measure called [Number of Persons].

  The [Breast Tumour Size] dimension has a regular relationship with the Breast Screening measure group. The [Breast Tumour Size] granularity is at unit size level - the unit of tumour size is in whole millimeters e.g. 1 mm, 2 mm, 3 mm, so the dimension keys are 1, 2, 3, so on.

  Does that help?

  Dannie

  
  

  • Reply
  • Quote

   
• Sunday, February 17, 2013 11:00 PM

   

   
  

  0

  Anyone?

  ----

  I have the following measure (courtesy of Darren Gosbell) which calculates median age without using the MEDIAN function, relying instead on counts pre-aggregated at the lowest level, i.e single year ages ... 30, 31, 32, 33, 34 yr old...

  CREATE MEMBER CURRENTCUBE.[MEASURES].[Median Age at Screening]
  AS 
      IIF ([Number of persons] = 0 or [Number of persons]= NULL,NULL ,
          HEAD(Filter([Age at Screening].[Age].[Age],  
               SUM(null:[Age at Screening].[Age].CurrentMember,
                  [Number of persons])>= ([Age at Screening].[Age].[All],[Number of persons])/2)
                  ,1).item(0).item(0).MemberValue + " yrs"    
          ),
  VISIBLE = 1; 

  NB... [Age at screening] is one of several dimension roles of the dimension [Age], shown below.

  This works beautifully and is much faster than the native Median function.

  Now I'm trying to translate this to Median Breast Tumour Size as follows, but I'm not getting any values from it (null in SSAS, #VALUE in Excel).

  CREATE MEMBER CURRENTCUBE.[MEASURES].[Breast Tumour Size Median (mm)]
  AS 
      IIF ([Number of persons] = 0 or [Number of persons]= NULL,NULL ,
          HEAD(Filter([Breast Tumour Size].[Breast Tumour Size].[Breast Tumour Size Key],  
               SUM(null:[Breast Tumour Size].[Breast Tumour Size].[Breast Tumour Size Key].CurrentMember,
                  [Number of persons])>= ([Breast Tumour Size].[Breast Tumour Size].[All],[Number of persons])/2)
                  ,1).item(0).item(0).MemberValue + " mm"    
          ),
  VISIBLE = 1;  

  Breast Tumour Size Key is also at the lowest level (1, 2, 3, 4... 100 mm, 9999 for unknown).

  What am I doing wrong?

  Dannie

  

  

  
  
  • Reply
  • Quote

   
• Monday, February 18, 2013 1:08 AM

   

   
  

  0

  I think you problem is in this line

   SUM(null:[Breast Tumour Size].[Breast Tumour Size].[Breast Tumour Size Key].CurrentMember,

  You want the currentmember of the [Breast Tumour Size].[Breast Tumour Size] hierarchy

  ie, try

   SUM(null:[Breast Tumour Size].[Breast Tumour Size].CurrentMember,

  By the way, you don't need to include  or [Number of persons]= NULL, as NULL=0 in SSAS measures.

  http://RichardLees.blogspot.com

  ---

  Richard

  • Reply
  • Quote

   
• Monday, February 18, 2013 5:56 AM

   

   
  

  0

  Thanks Richard

  
  That was one problem among others, you're right, but not quite there yet.

  
  After some fiddling, I finally got it to display something other than a null. The following always returns "All mm", the All Member:
  
  

  
  CREATE MEMBER CURRENTCUBE.[MEASURES].[Breast Tumour Size Median (mm)]
  AS 
          HEAD(Filter([Breast Tumour Size].[Breast Tumour Size Key],  
  
               SUM(null:[Breast Tumour Size].[Breast Tumour Size Key].CurrentMember,
                  [Breast Screen Tumour Size Value Count]) >= [Breast Screen Tumour Size Value Count]/2 )
  
                  ,1).item(0).item(0).MemberValue + " mm"
  
      ,
  VISIBLE = 1;  

  I've replaced [Number of persons] - which includes people with no (or empty) breast tumour size values - with [Breast Screen Tumour Size Count], a count of rows with non-empty tumour sizes.
  
  The calculation returns the All Member regardless of the filter condition, e.g. even when I set the filter to SUM(null:...) >= 0, which should really return the first non-empty member.
  
  

  I've tested the cumulative sum formula "SUM(null:...CurrentMember..." and it works, so that seems to point to the return set as the problem, but changing the filter to Filter([Breast Tumour Size].[Breast Tumour Size].[Breast Tumour Size Key]... again returns a null.
  
  Seems awfully close, now that it actually returns a member value, though the wrong one... any ideas?
  
  

  Dannie

  • Reply
  • Quote