Originally Published: 4 October 2023
Last Updated: 5 November 2026
Using UNION in DAX is a third option to combine data for analysis in Power BI
We recently showed how to do this with UNION in SQL and how to do similar to APPEND in Power Query
It isn’t always possible to do this in SQL such as if the datasets are coming from separate systems, and depending on the circumstances you may not want to do it in Power Query either. Doing it using DAX in Power BI to create the table is another alternative
Need Help with Complex DAX Calculations?
Writing DAX is one thing. Building a scalable, high-performing Power BI solution is another.
Our consultants help organisations create robust data models, optimise report performance, and develop advanced DAX measures that deliver trusted insights.
UNION in DAX Syntax
Before you can use the DAX command, the tables you want to union must be in the data model
We will use the same two sample data tables that we used for the Append example last week
the first covers store numbers 1 to 5 (SalesA)

And the second covering store numbers 5 to 10 (SalesB)

to create a new UNION table, go into the table view pane and click 'New Table'The syntax is: New Table Name = UNION(table1,table2) We will call the new table 'Sales Combined' In the DAX formula bar we enter Sales Combined = UNION(SalesA,SalesB)
Once you press enter the DAX code runs and the new table is produced
We can see that the new table contain all 5 rows from SalesA and all 5 rows of SalesB
This is very straight forward in this example and simply combines the two datasets in a simple way
If you look carefully you can see that Store 5 Product 1 does have the same data for now we will assume these are not duplicates, but we will return to this to show how to deal with duplicates
Important Points to Note
For UNION to work there are a few considerations that must be in place, and these are essentially the same as UNION ALL in SQL
- The columns MUST be in the same order in both tables, if they are not it is possible that the UNION could still run, but give strange mixed results
- The data types of the columns must be compatible
- Column names are inherited from the first table, they do not have to be the same
- Tables must have the same number of columns
- Duplicates are not eliminated (we will explore this)
How to Remove Duplicates?
To demonstrate this we firstly need to remove our Source column
SELECTCOLUMNS(SalesA,"Product_ID",SalesA[Product_ID], "Store_ID", SalesA[Store_ID], "qty_sold", SalesA[qty_sold])
This DAX code just selects the three columns we need
We repeat this for both tables, then apply this into the union
Sales Combined = union(SELECTCOLUMNS(SalesA,"Product_ID",SalesA[Product_ID], "Store_ID", SalesA[Store_ID], "qty_sold", SalesA[qty_sold]),SELECTCOLUMNS(SalesB,"Product_ID",SalesB[Product_ID], "Store_ID", SalesB[Store_ID], "qty_sold", SalesB[qty_sold]))
We now see the 10 rows without the source column
To remove the duplicate for store 5, we can now use the DISTINCT command
Sales Combined = DISTINCT (union(SELECTCOLUMNS(SalesA,"Product_ID",SalesA[Product_ID], "Store_ID", SalesA[Store_ID], "qty_sold", SalesA[qty_sold]),SELECTCOLUMNS(SalesB,"Product_ID",SalesB[Product_ID], "Store_ID", SalesB[Store_ID], "qty_sold", SalesB[qty_sold])))
This results in 9 rows

You can see here that other table based operations can be used within UNION, here we use the select columns to only select the relevant columns for the union
This method would work with tables which have a different number of columns
UNION with More than Two Tables
Union needs to have at least 2 table references, but it is not limited to a fixed number
If we want to union three tables we just add more
Sales Combined Three = UNION(SalesA,SalesB,SalesA)
This example also shows that you can repeat the same table if you need to

Conclusion
With this third way to combine data tables in Power BI you are now equipped to perform your data consolidation
Having a seamless integration of multiple tables or queries is a great way to simplify onward analysis
Simon Harrison
Simon Harrison is the founder and director of Select Distinct Limited and a Business Intelligence expert.
He specialises in SQL Server architecture, Power BI, and enterprise data warehousing.
Subscribe for More Data & Analytics Tips
Watch short video tutorials, SQL walkthroughs, and Power BI timesavers on the Select Distinct YouTube channel.

The syntax is:
New Table Name = UNION(table1,table2)
We will call the new table 'Sales Combined'
In the DAX formula bar we enter
Sales Combined = UNION(SalesA,SalesB)
