Combining Tables in Power BI: Union, Except, and Intersect in DAX

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Power Query is often the engine used for combining data tables, especially using Merge or Append. However, sometimes, you might need to do that operation in DAX. An example of that is when you want to create that combination only virtually as part of a measure calculation that evaluates dynamically. In this article, I’ll explain three DAX functions and what are their meanings: Union, Except, and Intersect.

Sample Data

I have two really simple data tables, each with one column: Column 1;

sample data tables

For the operations below, each table can have more than one column. However, I keep it simple to understand.

Union

If you want to have all the data rows of the two tables appended to each other, you can use UNION function in DAX. This function, simply gets the two input tables, and returns the appended result.

The Union function is a tabular function, and cannot be used directly in a measure. it has to be used either in a calculated table or inside another function.

Union function in DAX

Union function does not remove duplicate values that might happen after the operation. You can use Distinct, or Values functions for that.

Intersect

Intersect only returns the rows that exist in both tables. All of those rows that exist in only one of the tables will be removed from the resultset. This is how you can use Intersect;

As you can see, the syntax that INTERSECT and UNION are used are exactly the same. The same rule applies on EXCEPT as well. For these three functions, you just need two input parameters; the two tables.

Intersect function in DAX

Except

For the UNION and INTERSECT, the order of passing the tables to the function doesn’t matter (the only impact would be the final order of items in the result set). However, for the Except, the order of tables is important.

If you want all rows from table1 that does not exist in table2, then you can write as below;

The result would be all rows that only exist in table1.

Except function in DAX

If you change the order of tables, then you get a different result;

This would be all rows that exists in table2 only.

Except function in DAX

Important considerations

In all of the functions above, you need two tables to have the same structure. The same structure means the same number of columns. The matching is based on the position of the column in the table.

If you are using the techniques above to create a calculated table, I strongly recommend you to have a look at Append and Merge transformations in Power Query. Often they can be a much better option if the purpose of this work is transformation. Only use it in DAX if you are targeting a dynamic combine approach.

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply