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;
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.
UNION(Table1,Table2)
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 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;
INTERSECT(Table1,Table2)
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.
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;
EXCEPT(Table1,Table2)
The result would be all rows that only exist in table1.
If you change the order of tables, then you get a different result;
EXCEPT(Table2,Table1)
This would be all rows that exists in table2 only.
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
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.