Except Function - MDX Analysis Service 2005

SQL Server Analysis Service 2005
Except in MDX

Usage:-
Except (Set1, Set2, ALL)




When we supply two sets this function evaluates first set and removes tuples which are in the second
set.

If we specify ALL then the function will retain duplicates from the first set but duplicates from second set will be removed.

Example

SELECT [Measures].[Reseller Sales Amount] ON 0 ,
[Geography].[Country].MEMBERS ON 1
FROM [Adventure Works];

This will return all country with ALL Member "All Geographies".
Now we want to remove this "All Geographies"

We will use Except here.

SELECT [Measures].[Reseller Sales Amount] ON 0 ,
EXCEPT([Geography].[Country].MEMBERS,[Geography].[Country].[All Geographies]) ON 1
FROM [Adventure Works]
;

This will remove "All Geographies" and display all countries.