MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

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] ...