NaturalInnerJoin And NaturalLeftOuterJoin DAX Functions In Excel 2016

Continuing my series on new DAX functions in Excel 2016, here are two more: NaturalInnerJoin() and NaturalLeftOuterJoin(). Both do pretty much what you’d expect.

Consider the following two tables in an Excel worksheet, called ColourFruit and FruitPrice:

image

With these tables loaded into the Excel Data Model as linked tables, the next step is to create a relationship between the tables on the Fruit column:

image

Both functions only work with two tables that have an active relationship between them, and both take two tables from the Excel Data Model as parameters. Once you’ve done that you can use these functions in a DAX query.

The queries

evaluate naturalinnerjoin(ColourFruit,FruitPrice)

and

evaluate naturalinnerjoin(FruitPrice,ColourFruit)

…both perform an inner join between the two tables on the Fruit column and both return the same table:

image

The query

evaluate naturalleftouterjoin(ColourFruit,FruitPrice)

…returns

image

The query

evaluate naturalleftouterjoin(FruitPrice,ColourFruit)

…returns:

image

For NaturalLeftOuterJoin() the table given in the first parameter is on the left-hand side of the left outer join, so all rows from it are returned, whereas the table in the second parameter is on the right-hand side of the join so only the matching rows are returned.

9 thoughts on “NaturalInnerJoin And NaturalLeftOuterJoin DAX Functions In Excel 2016

  1. Looking at these new functions and Power BI Designer the era of DAX and PowerPivot is vanishing and M seems to be their new king. What do you think?

    • I don’t think so – M and DAX do two different things. Some types of calculation you could do in both and in those cases I prefer M; some calculations can only be done in DAX though. The fact that Power Pivot and DAX functionality hasn’t appeared in the Power BI Designer yet doesn’t mean anything – it will be appearing soon.

  2. Your creation of a relationship between the tables in the Data Model should not be required for the join condition of NaturalInnerJoin and NaturalLeftOuterJoin to work – it should automatically be defined by the common Fruit column. However I find that without that relationship the query fails with an error that there is no common column. Do you also get this error if the relationship is removed? Thanks

  3. In SSAS 2016 RC0, NaturalInnerJoin and NaturalLeftOuterJoin does NOT work unless you specify a relation in the tabular model. The error message you get is “No common join columns detected. The Join funtion “NaturalInnerJoin” requires at-least one common join column.”. The message disappears directly after you create a relation.

    • If what you say is correct then why does The Definitive Guide to DAX by Russo & Ferrari (Chapter 9) state that these functions are used “when there are no relationships in the data model between the two tables to join” and is also supported by their example with a many to many common column? The description of these functions on support.office.com also says that tables are joined on common columns (by name) without any mention of the need for a relationship.

  4. Thanks, I’d seen that remark about lineage at msdn but found it a bit too brief to understand at first – an example as provided for all the other new DAX functions would have helped!

    It now makes sense. Joining tables that are not directly related simply requires both to have a many to one relationship with a reference table that contains all values for the common column. Marco and Alberto’s example worked without a reference table because their join tables were not already in the data model – they were defined as variables in the join query.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s