DAX does not offer a UNION function or operator. There are a few scenarios where this would be useful.

# The scenario

Consider the following table called *Games*.

This is a very simple table. Basically, this table represents a relation between players, which can be stated as “*Player 1* played against *Player 2*”. For now, I will ignore the meaning of why one player is listed in one column or the other.

In order to analyze this data, we will need a *Player* dimension, representing the set of all players, regardless of whether they were labeled as *Player 1* or *Player 2*.

If the Games table came from a SQL connection, then we might as well use the following query to get the desired result.

SELECT [Player 1] AS Player FROM Games

UNION

SELECT [Player 2] FROM Games

The table would look like this.

(Remember that UNION only returns distinct rows).

# A DAX solution

I already mentioned Excel 2013 supports DAX queries, and the resulting tables can be re-used in the data model. Let us take a look at what we need to achieve the desired result.

First thing we need to care about, is that our result set might have more entries than our *Games* table. We will need to generate at least one row per distinct value in player 1 and 1 row per distinct value in player 2.

Now, imagine you create a PivotTable based on the Games table and put [Player 1] and [Player 2] on rows. This is what you will get:

For every distinct value of [Player 1], Excel will create a group, list all the values for [Player 2] within this group, and will add a row for the sub-total.

Since we cannot use the body range of a pivot table, as a data source, we will do this with a DAX query.

SUMMARIZE and ROLLUP will allow us to mimic this behavior.

The following DAX statement

EVALUATE

SUMMARIZE(

Games

, [Player 1]

, ROLLUP( [Player 2])

, “Player”

, IF (ISSUBTOTAL(Games[Player 2]),[Player 1] , [Player 2])

)

will return the following table:

Note that ROLLUP([Player 2]) instructs PowerPivot to generate a subtotal row for the current value of [Player 1]. We also create a calculated column named *Player*, that will just take the value of [Player 2] for “regular” rows, and the value of [Player 1] for sub-totals. Also, ISSUBTOTAL allows us to differentiate subtotal rows from rows where [Player 2] is blank.

Finally, we just want to get the unique values in the Player column. For that, we will use, once again, the SUMMARIZE function.

# The final query

EVALUATE

SUMMARIZE(

SUMMARIZE(

Games

, [Player 1]

, ROLLUP( [Player 2])

, “Player”

, IF (ISSUBTOTAL(Games[Player 2]),[Player 1] , [Player 2])

)

, [Player]

)