Except

Returns a subset containing the differences between two sets, optionally retaining duplicates. The two input sets must have identical dimensionality.

Syntax

Except ( set1, set2 [,ALL] )

Example

Except( {[New York], [California], [Florida], [California]},
        {[Oregon], [Washington], [California], [Florida]})

returns {[New York]}.

Except( {[New York], [California], [Florida], [California]},
        {[Oregon], [Washington], [California], [Florida]}, ALL)

returns {[New York], [California]}.

The following query returns Actual Sales and Profit numbers for the level-0 markets that are not defined as "Major Market."

SELECT
 {[Measures].[Sales], [Measures].[Profit]} 
ON COLUMNS,
 Except(
   [Market].Levels(0).Members, 
   UDA (Market, "Major Market")
 ) ON ROWS
FROM Sample.Basic
WHERE {([Year].[Qtr1], [Scenario].[Actual])}

This query returns the grid: