MDX Query for Filtering Non Empty Values

Question

I need to create a filter in Performance point Dashboard to get only non empty values. I am trying to write a MDX Query for selecting the values

SELECT NONEMPTY{[H School ].[Dist Name], [H School ].[School Name]}

FROM  [Early Cube ]

but it is not filtering the data

Answer 1

You need to check for non-empty cells against some measure. In other words, the dimension members need to be empty or non-empty against 'some set', such as a measure. There are two ways of doing this depending on whether you want your measures to be shown in your result.

1. NON EMPTY clause in SELECT:

   It displays cells obtained from intersection between measures on one axis and dimension members on the other axis, thereby eliminating the (null) values.

   SELECT 
   
   /*Measures*/
   {[Measures].[Customer Count]} ON 0,
   
   /*NON EMPTY clause against Dimension members*/
   NON EMPTY {[Customer].[Customer Geography].[City]} ON 1 
   
   FROM [Adventure Works];
   

2. NONEMPTY() function:

   Returns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set.

   SELECT 
   
   {} ON 0,
   
   /*NONEMPTY() FUNCTION*/
   NONEMPTY([Customer].[Customer Geography].[City], [Measures].[Customer Count]) ON 1 
   
   FROM [Adventure Works]
   

More detailed difference between these two approaches can be found here and here.

Answer 2

Here in the query:

SELECT NONEMPTY([H School ].[Dist Name], [H School ].[School Name])

FROM  [Early Cube ]

Can you rewrite this as:

SELECT NONEMPTY([H School ].[Dist Name]*[H School ].[School Name], <<add your measure/context here if you do not want to use the default measure>>)

FROM  [Early Cube ]