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 SQL Server Management Studio (SSMS) and check that you are working on the Adventure Works DW 2016 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...