Using NOT IN in an MDX query

Question

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 !

score 5 · Accepted Answer · answered Apr 13 '13 at 05:50

5

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

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

answered Apr 13 '13 at 05:50

Marc Polizzi

9,275
3
36
61

I did try that , like I mentioned , but that dint help either. – Rohan Grover Apr 13 '13 at 14:38
How about Except ( {[DIM1].[A], [DIM1].[B], [DIM1].[C]}, {[DIM1].[A]} ) ? Does it work ? – Marc Polizzi Apr 13 '13 at 14:42
Yes! That worked. Something wrong with the way I reference all members {[DIM1].MEMBERS} set then? Thanks a lot Marc, for all your help. – Rohan Grover Apr 15 '13 at 13:36
1

OKAY! I figured what the problem was. The Members function returns all members of the given dimension except calculated but including the 'All' member. So even though my Filtered set did not have the points I excluded, the 'All' member was including their values in the calculated measures amount. I used the Children function instead and now it works fine, with both , filter() and except() – Rohan Grover Apr 15 '13 at 14:26

score 0 · Answer 2 · answered Nov 22 '19 at 15:20

I am building a custom MDX builder and I had gotten to the stage of 1..n FILTER constructs working. I tried to add EXCEPT around it and the query fired but the EXCEPT was ignored.

MDX query works but ignores the EXCEPT clause

I tried <> to, see my link/answer and it works nicely (and also means it can be chained together (working for many values I wanted ignored/filtered).

Using NOT IN in an MDX query

2 Answers2