[SOLVED] Using NOT IN in an MDX query

Using NOT IN in an MDX query

I am trying to filter out some points from a dimension in my MDX select query. I have used the Filter(, ) function as documented at http://mondrian.pentaho.com/documentation/mdx.php . The MDX being run is as follows

  SELECT {[Measures].[AMOUNT]} on 0,  
         {Filter ([DIM1].MEMBERS, [DIM1].CurrentMember NOT IN {[DIM1].[A], [DIM1].[B], [DIM1].[C]})} on 1,
         {[DIM2].[S]} on 2, 
         {[DIM3].[EFO]} on 3, 
         {[CURRENCY].[EUR]} on 4
  from [CUBE]

But Measures.AMOUNT is still taking Dim1.A, Dim1.B and Dim1.C into consideration. Could you kind folks please point out what is wrong with my syntax. I have also tried the Except(,) .

Best, Rohan

UPDATE :

Thanks to Marc , for pointing me in the right direction. I tested some more and found out that the Members function also returns a point 'All' which includes all the points on my dimension. So hence, even though the Filter() or the Except() functions were filtering the points properly, the 'All' point still contained the value for all points and hence was including them in the Measure.AMOUNT calculation. I instead used the Children function and that does not return the 'All' point so I see the desired result. Updated MDX's below

Working MDX Using Filter and NOT IN ( WARN: NOT IN is an Mondrian specific operation )

 SELECT {[Measures].[AMOUNT]} on 0,  
         {Filter ([DIM1].Children, [DIM1].CurrentMember NOT IN {[DIM1].[A], [DIM1].[B], [DIM1].[C]})} on 1,
         {[DIM2].[S]} on 2, 
         {[DIM3].[EFO]} on 3, 
         {[CURRENCY].[EUR]} on 4
  from [CUBE]

Working MDX Using except

 SELECT {[Measures].[AMOUNT]} on 0,  
         {except ( {[DIM1].Children}, {[DIM1].[A], [DIM1].[B], [DIM1].[C]} )} on 1,
         {[DIM2].[S]} on 2, 
         {[DIM3].[EFO]} on 3, 
         {[CURRENCY].[EUR]} on 4
  from [CUBE]

Thanks !

Solution

Instead of filter, I would use the Except function as following :

Except ([DIM1].MEMBERS, {[DIM1].[A], [DIM1].[B], [DIM1].[C]} )