Skip to main content

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

See Also

FeedbackOpens in a new tab