Search code examples
ssasmdxmedian

How to use Median function in MDX?

I have a SQL Server Analysis Services cube ( Multidimensional). This cube represents Survey feedback. A student gives feedback on course for a given term (period of time ). There are 19 questions with a score from 1 to 5 and I try to get the median of these answers. Just to precise, I am looking at the median of answer for each {Question, Course} just for one period of time ( term).

My cube model looks like this : model

And the query I am running is :

with calculated member [Median] as
MEDIAN(existing NONEMPTY([Course].[COURSE NAME].children*[Survey 
Question].[SURVEY QUESTION NUMBER].children),[Measures].[NUMERIC 
RESPONSE])


Select non EMPTY{[Course].[COURSE NAME].children} on rows,
NON EMPTY{[Survey Question].[SURVEY QUESTION NUMBER].children*{[Measures]. 
[NUMERIC RESPONSE],[Median]}} on columns
from [EDW]
where [Term].[TERM].&[5515]

The result doesn't show anything on the Median as if it was null. If anyone has an idea around this issue I will love to hear it.

Regards

Vincent

Solution

  • This is your query with a couple of small changes - unfortunately not used Median before and I do not have AdvWks to test on so feeling around in the dark a little:

    WITH
MEMBER [Measures].[MYMedian] AS
  MEDIAN(
    NONEMPTY(
        [Course].[COURSE NAME].[COURSE NAME].MEMBERS
       *[Survey Question].[SURVEY QUESTION NUMBER].[SURVEY QUESTION NUMBER].MEMBERS
       ,[Measures].[NUMERIC RESPONSE]
    )
    ,[Measures].[NUMERIC RESPONSE]
  )
SELECT
NON EMPTY 
[Course].[COURSE NAME].children on rows,
NON EMPTY  
   [Survey Question].[SURVEY QUESTION NUMBER].children
  *{
     [Measures].[NUMERIC RESPONSE]
    ,[Measures].[MYMedian]
   } 
on columns
FROM [EDW]
WHERE [Term].[TERM].&[5515];