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] )
Parameter | Description |
---|---|
set1 | A set to compare with set2. |
set2 | A set to comparet with set1. |
ALL | The optional ALL flag retains duplicates. Matching duplicates in set1 and set2 are eliminated. |
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:
(axis) | Sales | Profit |
---|---|---|
Connecticut | 3472 | 920 |
New Hampshire | 1652 | 202 |
Oregon | 5058 | 1277 |
Washington | 4835 | 1212 |
Utah | 4209 | 744 |
Nevada | 6516 | 775 |
Oklahoma | 2961 | 718 |
Louisiana | 2906 | 773 |
New Mexico | 1741 | 4 |
Wisconsin | 4073 | 913 |
Missouri | 3062 | 399 |
Iowa | 6175 | 2036 |