Downloads
DAXTable constructionIntermediate

UNION

Appends the rows of several tables that have the same number of columns.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals