EXCEPT (MDX)

Examines two sets and returns a set that consists of the elements of the first set, except for any elements that are also in the second set. This function optionally eliminates duplicates in that set.

Returned Type

This function returns a set.

Syntax and Details

EXCEPT(set_expression1, set_expression2, ALL)

EXCEPT(set_expression1, set_expression2)

set_expression1 and set_expression2 are expressions that evaluate to sets.
The optional keyword ALL, if included, specifies that all duplicates should be retained. By default, if the first set includes any duplicate elements, only the first of those is included.

If set_expression2 includes elements that are not in set_expression1, those elements are ignored. The returned set includes only elements from set_expression1.

Example

Consider the following query which defines two named sets:

WITH SET set1 AS '{allerd.eggs,allerd.eggs,allerd.soy,allerd.wheat}' 
SET set2 AS '{allerd.[diary products],allerd.pollen,allerd.wheat}' 
SELECT MEASURES.[%COUNT] ON 0, EXCEPT(set1,set2) ON 1 FROM patients

                             Patient Count
1 eggs                                  451
2 soy                                   462

This query shows the members of set1 that are not also in set2. Notice that the member allerd.eggs is listed twice within set1, but is shown only once in the result.

In contrast, the following variation uses the ALL keyword:

WITH SET set1 AS '{allerd.eggs,allerd.eggs,allerd.soy,allerd.wheat}' 
SET set2 AS '{allerd.[diary products],allerd.pollen,allerd.wheat}' 
SELECT MEASURES.[%COUNT] ON 0, EXCEPT(set1,set2,ALL) ON 1 FROM patients

                             Patient Count
1 eggs                                  451
2 eggs                                  451
3 soy                                   462

EXCEPT (MDX)

Returned Type

Syntax and Details

Example

See Also