Power BI: Using DAX Union() Function

BY: David Rohlfs

Introduction

There are many reasons why you may want to put two tables into one, and because of it there are a couple of different methods that you can use to do this. Generally you will use an append or merge function, or you may even go into using a DAX Join() Function. But in this blog we are going to go over a method of Appending that uses the DAX Union() Function.

In this blog we will go over when to use this function, some common problems that you may face when using this function, and a short walkthrough demonstrating how to use the Union() function.

This blog is meant to be in connection with two other EPM Strategy blogs about Append and Merge, and the DAX Join() functions. To read more about these, there will be a link at the bottom of this page.

 

When to use it

The Union() function works most like an append. This is nice because the Join() functions will work more like a merge, so if you are going to learn one, you should take time to learn the other.

If you don’t know how an append works or are unfamiliar with the concept of appending queries in general, I suggest that you go and read through the append and merge blog from EPM Strategy. This blog is not going to go over some of the basics that you may not be familiar with.

Here is a view of the similarities between a Union() function and a normal Append:

 

You can see that in this scenario they are the same, but they were done using different methods.

The nice thing about using a DAX function is that it can help with performance and usability if you have a very large semantic model. Unfortunately, using a DAX function does have many downfalls for most report builders:

• If you pass on a report to someone that doesn’t know DAX, they will have to rebuild the append.
• If you are not careful with the amount of new columns and new tables from DAX, you can hurt your reports’ performance.
• There are many common problems that you may experience with this function (Read Common Problems below).

One of the pros of using this function is that you can have duplicating rows. Though this doesn’t seem like it may be good, it means that using this function will not automatically remove any duplicated rows from you.

 

Common Problems

In Microsoft’s DAX guide they list out several limitations for the Union() function.

• The tables need to have the same number of columns.
• They need to have the same column names.
• If the data types are different, then you might get an error (depending on a case-by-case situation).
• Union() does not support Direct Query.

This function is not meant for unclean data, and it may not be best for many situations. But if the circumstances are right for a Union(), and you want to use DAX, this may be the answer.

 

Walkthrough

This is a simple walkthrough showing how you implement the Union() function into your report.

To start, you can click somewhere over in your fields pane and select “New Table” under your table tools ribbon.

 

Then you can write your Union() equation. The format is Table Name = Union(‘Table1’,’Table2’)

 

If you do get an error at this point, go back and read “Common Problems” above to see if you meet all the criteria.

Now you can create your table with the DAX table.

 

Conclusion

This DAX function is very simple to work with and understand once you meet all the criteria and understand how a normal append works. Just like any other function in Power BI, this function is meant to give you more options than your normal append and let you try a different method of completing an append.

 

Link to Append and Merge Blog

Link to DAX Join Function Blog

Comments