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:
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:
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:
The query
evaluate naturalleftouterjoin(ColourFruit,FruitPrice)
…returns
The query
evaluate naturalleftouterjoin(FruitPrice,ColourFruit)
…returns:
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.
you are genius for finding these formulas when I couldn’t even find these on MS website
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.
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
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.
My only guess would be that it’s a bug in the dax validation engine in RC0 producing this error.
It’s a bug in Marco and Alberto’s bug, although only a minor one 🙂
I’m told that for NaturalInnerJoin to work the two columns must not only have the same name but also have the same lineage (see P248-250 of the Definitive Guide To DAX) and therefore share the same dictionary. This is hinted at on this page: https://msdn.microsoft.com/en-us/library/dn802543.aspx but not properly explained.
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.