UNION
Appends the rows of several tables that have the same number of columns.
UNION(<table1>, <table2> [, <table3>] ...)Returns: A table. Duplicate rows are kept.
What it does
UNION stacks tables on top of each other. The tables must have the same number of columns, with compatible data types in each position. The column names come from the first table. Duplicate rows are kept, so a customer on both of two lists appears twice. Wrap the result in DISTINCT to remove the repeats.
Syntax and arguments
- tables: two or more tables, each with the same number of columns in the same order.
Example
A combined list of customers who bought in a store or online, with each customer listed once.
Store or Online Customers =
DISTINCT (
UNION (
CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), Channel[ChannelName] = "Store" ),
CALCULATETABLE ( VALUES ( Sales[CustomerKey] ), Channel[ChannelName] = "Online" )
)
)
Each part lists the customers in one channel. UNION stacks the two lists, and DISTINCT leaves one row per customer who bought in either channel.
Example: a second use
A single list of store names from two sources: the store master and a budget table.
All Store Names =
DISTINCT (
UNION (
VALUES ( Stores[StoreName] ),
VALUES ( Budget[StoreName] )
)
)
This assumes a Budget table with a StoreName column, which is not part of the Contoso sample. A store that appears in only one source still shows in the list.
Things to watch
UNIONdoes not remove duplicates. Wrap it inDISTINCTwhen you need unique values.- The column count and data types must match by position. The names come from the first table.
UNIONis not a join. It stacks rows. To put columns side by side, use relationships orTREATAS.- In a measure, the result is a table, so count it with
COUNTROWS. - Changing the order of the tables changes only the column names, not the rows.
In business terms
Combining lists from two systems, such as the general ledger and a subledger, stacking actuals from two periods before a comparison, and building one customer or vendor list for a clean-up project.