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
