Home » DAX » NaturalInnerJoin and NaturalLeftOuterJoin DAX Functions

NaturalInnerJoin and NaturalLeftOuterJoin DAX Functions

Join through DAX functions in Power BI

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-

Relationship

Relationship



-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.

Create a new table in Power BI

Create a new table in Power BI

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.

NaturalInnerJoin DAX

NaturalInnerJoin DAX



-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')
NaturalLeftOuterJoin DAX

NaturalLeftOuterJoin DAX

You can see in above screen shot it returns all records from the left table, and the matched 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.



Leave a Reply