Implementing the NOT IN set logic

There are times when we want to exclude some members from the result. We can perform this operation using a set of members on an axis or using a set of members in a slicer, that is, the WHERE part of an MDX query. This recipe shows how to do the latter, that is, how to exclude some members from a set in a slicer. The principle is the same for any part of an MDX query.

Getting ready

Start a new query in SSMS and check that you're working on the Adventure Works DW 2012 database. Then type in the following query and execute it:

SELECT
   { [Measures].[Reseller Order Count] } ON 0,
   NON EMPTY
   { [Promotion].[Promotion].MEMBERS }
   DIMENSION PROPERTIES
       [Promotion].[Promotion].[Discount Percent] 
   ON 1
FROM
   [Adventure Works]

The preceding query returns 12 promotions and all the top-level promotions on the rows axis. The DIMENSION PROPERTIES keyword is used to get additional information about members – each promotion's discount percent. However, the...