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 information about members each promotion...
