Power BI DAX #6: UNION

UNION is an interesting DAX formula. Simple yet powerful in what it makes possible for you to achieve. 

I will be demonstrating with a simplified version of one of my real world use cases for UNION.

Say you have created a Waterfall chart showing cashflow (or inventory quantity or staff addition/reduction, sales quantity) movement month on month through out the year and with the final position as at year end.

The (simplified) source table looks like this

But you want to see the opening cash (or opening stock or staff count at start of the year etc).

There are many ways to get it in if you are given that data but the way we will explore today is using UNION formula.

UNION is a DAX formula that allows you to append/join two or more tables. And in this demonstration I will show you a cool way to create a literal table in Power BI DAX (a table with rows of data that you type in yourself in the DAX).

Say you were given the opening cash position as N2m and you just want to plug that in and move on with your analysis and chart. If it were Microsoft Excel, that will be as simple as adding a new row in your table. How do we achieve the closest to that in Power BI?

Union Table = UNION('Cashflow Table',{(2019,"Opening Cash",2000000,0)})

{(2019,"Opening Cash",2000000,0)}

is the literal table part. And if I wanted multirow table it's just to put each row in the () separated by comma. For example:

{(2018,"Closing Cash",2000000,0),(2019,"Opening Cash",2000000,0)}

With this new table I can create the type of waterfall chart desired. Note though that if you have a very large table, you might want to create a UNION with a SUMMARIZE 'd copy of the table rather than duplicating the entire table.

You can watch the much more engaging video demonstration of this: https://youtu.be/zNCYmSLM2cg