NaturalInnerJoin and NaturalLeftOuterJoin DAX Functions

NATURALINNERJOIN & NATURALLEFTOUTERJOIN both DAX functions are used to perform the joins between two tables, these functions comes under Table manipulation functions category.

Common Usage note for both functions:

• Columns being joined on must have the same data type in both tables.
• These functions does not support Direct Query mode.
• The names of the columns that define the relationship need to be different.

Consider the following two tables-

Table: Product

Id	Product	Qty
1	Bike	30
2	Car	12
3	AC	3
4	Bus	20

Table: Price

Item	Price
Bike	60000
Bus	1500000
AC	50000
Car	500000
Truck	2500000
Lights	3000

Create a relationship between both tables-

-NATURALINNERJOIN

Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables.

The NATURALINNERJOIN function joins the left table with right table using the Inner join semantics.

This function returns a table with matching values in both tables.

Syntax:

NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)

Parameters:

leftJoinTable: A table expression defining the table on the left side of the join.

rightJoinTable: A table expression defining the table on the right side of the join.

Follow these steps in order to implement NATURALINNERJOIN-

Step-1: Go to the Modeling tab > click on create a new table icon.

Step-2: Now write a DAX function for inner join-

InnerJoin = NATURALINNERJOIN('Product', 'Price')

Step-3: Now you can see the inner join result, returns new table with matching values in both tables.

-NATURALLEFTOUTERJOIN

Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables.

The NATURALLEFTOUTERJOIN function joins the left table with right table using the left outer join semantics.

This function returns all records from the left table (table1), and the matched records from the right table (table2).

Syntax:

NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>)

Parameters:

leftJoinTable: A table expression defining the table on the left side of the join.

rightJoinTable: A table expression defining the table on the right side of the join.

Follow these steps in order to implement NATURALLEFTOUTERJOIN –

Step-1: Go to the Modeling tab > click on create a new table icon.

Step-2: Now write a DAX function for inner join-

LeftOuterJoin = NATURALLEFTOUTERJOIN('Price', 'Product')

You can see in above screen shot, it retrieves all records from the left table along with the matching records from the right table.

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.