Fixing Lineage issue when JOIN-ing tables in DAX

DAX has 2 functions that allow you to JOIN tables and columns i.e. NATURALINNERJOIN & NATURALLEFTOUTERJOIN, although there is no FULLOUTERJOIN you can mimic that by reading FULLOUTERJOIN IN DAX

When you try to combine 2 tables or columns you are presented with 2 issues:

1. Common Names

2. Data Lineage

In this blog I want to show 2 methods using which you can overcome these 2 situations.

You can download the PBIX from here: pbix

Data Model

Products

Customers

Sales

Solution 1 that uses vanilla DAX and will work with almost all the flavours of Analysis Services:

If I try to merge Products and Sales using NATURALINNERJOIN / NATURALEFTOUTERJOIN I will be presented with the first problem i.e. Common Names.

The reason is straight forward, we need to tell the engine what columns to use for joining, even though there is ProductKey in both Sales and Products the full name is Sales[ProductKey] and Products[ProductKey] which are different.

To make the names same we can use SELECTCOLUMNS on both tables while renaming the ProductKey columns and leaving others intact.

But that poses the second challenge i.e. Data Lineage

To resolve this issue we have a solution we can change the common columns structurally by modifying their lineage but before that here is a DAX code that utilizes the new INFO functions and shows that 2 columns have different lineage

Fixing the issue is simple, for

1. Integers/Numeric use + 0

2. Dates use + 0 or EDATE or any function that just returns Date without shifting it

3. Text use & ""

Since in this example we have Integers I will use + 0

And it works flawlessly

Dates & Sales: we can use + 0 or EDATE ( _, 0 )

Joining based on text fields we can use & ""

---

Solution 2 using TREATAS which is more efficient and won't work with way older version of PowerPivot and SSAS Tabular.

So the whole point of this blog is that the join columns have different names and different lineage now this is where TREATAS shines, not only it treats one or more columns as other columns it also completely changes the returned names.

As you can see as per the intellisense the Products[ProductKey] column is now Sales[ProductKey] and Products[ProductKey] reference is lost so this fixes 2 issues in one step

To fix the first example all we need is TREATAS and don't even need to alter the Sales table.

Dates & Sales:

Customers & Category

So there you go 2 solutions to fix this issue. You can download the PBIX from here: pbix