title: Using NOT IN in an MDX query
body: <p>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 <a href="http://mondrian.pentaho.com/documentation/mdx.php" rel="nofollow">http://mondrian.pentaho.com/documentation/mdx.php</a> . The MDX being run is as follows</p>
  
  <pre><code>  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]
  </code></pre>
  
  <p>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(,) . </p>
  
  <p>Best,
  Rohan</p>
  
  <p>UPDATE :</p>
  
  <p>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</p>
  
  <p>Working MDX Using Filter and NOT IN ( WARN: NOT IN is an Mondrian specific operation )</p>
  
  <pre><code> 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]
  </code></pre>
  
  <p>Working MDX Using except</p>
  
  <pre><code> 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]
  </code></pre>
  
  <p>Thanks !</p>