DAX

Dax Function: UNION

Q: Does UNION remove duplicate rows?

No, UNION retains all rows, including duplicates. Use the DISTINCT function to remove duplicates.

Category: Table Manipulation Functions

The UNION function in Power BI is a DAX (Data Analysis Expressions) function used to combine two or more tables into a single table. It appends the rows of the second table (or subsequent tables) to the first table.

Purpose

The UNION function consolidates data from multiple tables into one table for analysis or further calculations.
It helps create a unified view of similar data stored across different tables.

Type of Calculations

Appending Rows: Merges rows from multiple tables into one table.
Flattening Data: Useful for combining datasets of the same structure.
Data Wrangling: Facilitates analysis of distributed data by consolidating it.

Practical Use Cases

Merging Data: Combine data from multiple sources with identical structures.
Creating Master Tables: Generate a master dataset from multiple subsets.
Union of Filtered Data: Combine filtered subsets of a table for focused analysis.


UNION(<table1>, <table2>[, <table3>, ...])</table3></table2></table1>

Parameter	Type	Description
Table1	Table	The first table to be included in the union.
Table2	Table	The second table to be included in the union.
Table3, …	Table	(Optional) Additional tables to be included in the union.

How Does UNION Dax Works

Input Tables: The function accepts tables with the same number of columns and compatible column data types.
Row Appending: Rows from the second and subsequent tables are appended to the first table in sequence.
Output Table: The resulting table includes all rows from the input tables without any deduplication.

Key Consideration:
The column names in the resulting table are inherited from the first table.

Logical Principle

The UNION function is analogous to appending rows in Excel or SQL UNION ALL, where the order of the rows is preserved but duplicates are retained.

Example:

UNION(
SELECTCOLUMNS(Sales2022, "Year", Sales2022[Year], "Revenue", Sales2022[Revenue]),
SELECTCOLUMNS(Sales2023, "Year", Sales2023[Year], "Revenue", Sales2023[Revenue])
)

Effect:
Combines rows from Sales2022 and Sales2023 into a single table.

What Does It Return?

Type: Table.
Content: A table containing rows from all the input tables, combined vertically. Duplicate rows are not removed.

When Should We Use It?

To consolidate data for uniform analysis.
For merging partitioned datasets (e.g., sales data by year).
In scenarios requiring combined results for comparison or visualization.

Examples

Basic Usage :

Combine Two Tables:


UNION(
TableA,
TableB
)

Result: Returns a table with all rows from TableA and TableB.

Column Usage

Combine Filtered Subsets:


UNION(
FILTER(Sales, Sales[Year] = 2022),
FILTER(Sales, Sales[Year] = 2023)
)

Effect: Creates a table with sales data for 2022 and 2023.

Advanced Usage

Using Calculated Tables:


UNION(
SELECTCOLUMNS(RegionA, "Region", RegionA[Name], "Revenue", RegionA[Sales]),
SELECTCOLUMNS(RegionB, "Region", RegionB[Name], "Revenue", RegionB[Sales])
)

Result: Combines two datasets with specific columns and consistent naming.

Tips and Tricks

Ensure all input tables have the same structure (columns and data types).
Use SELECTCOLUMNS to align column names and types if they differ.
Column mismatches will result in an error.
The UNION function does not deduplicate rows—use the DISTINCT function if needed.

Performance Impact of UNION DAX Function:

Combining large tables can impact performance. Consider filtering input tables before using UNION.
Avoid combining excessively large tables unless necessary for calculations or reports.

Related Functions You Might Need

Function	Description
`INTERSECT`	Returns rows that are common between two tables.
`EXCEPT`	Returns rows from the first table that are not in the second.
`SELECTCOLUMNS`	Returns a table with renamed or selected columns.
`DISTINCT`	Returns a table with distinct rows from the input table.

Want to Learn More?
For more information, check out the official Microsoft documentation for UNION You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the UNION function do in Power BI?

The UNION function combines rows from multiple tables into a single table.

2. Does UNION remove duplicate rows?

No, UNION retains all rows, including duplicates. Use the DISTINCT function to remove duplicates.

3. What happens if the column structures differ?

The UNION function requires input tables to have the same number of columns with compatible data types. Mismatched columns will cause an error.

4. Can I combine more than two tables with UNION?

Yes, you can pass additional tables as parameters to UNION.

5. How does UNION differ from SQL UNION?

DAX UNION is equivalent to SQL UNION ALL because it retains duplicate rows.

Dax Function: TREATAS

Dax Function: VALUES