UNION (MDX)
Returns a set that consists of the elements of the two given sets, optionally eliminating duplicates.
Returned Type
This function returns a set.
Syntax and Details
UNION(set_expression1, set_expression2, ALL)
Or:
UNION(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 returned set includes any duplicate elements, only the first of those is included.
Example
Consider the following query which defines two named sets:
WITH SET set1 AS '{allerd.eggs,allerd.soy,allerd.wheat}'
SET set2 AS '{allerd.[dairy products],allerd.pollen,allerd.soy,allerd.wheat}'
SELECT MEASURES.[%COUNT] ON 0, UNION(set1,set2) ON 1 FROM patients
Patient Count
1 eggs 451
2 soy 462
3 wheat 479
4 dairy products 463
5 pollen 447
This query shows all the members that are in set1 and set2.
In contrast, consider the following variation, which uses the ALL keyword to keep duplicates:
WITH SET set1 AS '{allerd.eggs,allerd.soy,allerd.wheat}'
SET set2 AS '{allerd.[dairy products],allerd.pollen,allerd.soy,allerd.wheat}'
SELECT MEASURES.[%COUNT] ON 0, UNION(set1,set2,ALL) ON 1 FROM patients
Patient Count
1 eggs 451
2 soy 462
3 wheat 479
4 dairy products 463
5 pollen 447
6 soy 462
7 wheat 479
Finally, you can of course use more interesting sets as arguments. For example:
WITH SET set1 AS 'TOPCOUNT(homed.city.members,5,MEASURES.[avg allergy count])'
SET set2 AS 'TOPCOUNT(homed.city.members,5,MEASURES.[avg age])'
SELECT MEASURES.[%COUNT] ON 0, UNION(set1,set2) ON 1 FROM patients
Patient Count
1 Juniper 1,197
2 Spruce 1,135
3 Centerville 1,155
4 Redwood 1,144
5 Magnolia 1,156
6 Cedar Falls 1,188
7 Elm Heights 1,266
8 Pine 1,139
