NATURAL LEFT OUTER JOIN in DAX – Complete Step-by-Step Guide

What Is NATURALLEFTOUTERJOIN in DAX?

NATURALLEFTOUTERJOIN combines two tables by matching columns that have the same column name and compatible data lineage. It behaves like a left outer join: all rows from the left table are retained, while matching values from the right table are added when a matching key is available.

The basic syntax is:

NATURALLEFTOUTERJOIN(
    <Left Table>,
    <Right Table>
)

1. Performing a Natural Left Outer Join

Query

// NATURAL LEFT OUTER JOIN

EVALUATE
    NATURALLEFTOUTERJOIN(
        students_natural,
        payments
    )

Step-by-step explanation

Step 1: EVALUATE
EVALUATE tells DAX to return the result of the table expression as a query result.

Step 2: NATURALLEFTOUTERJOIN
NATURALLEFTOUTERJOIN combines the two supplied tables based on their common join column(s).

Step 3: students_natural
students_natural is the left table. Because this is a left outer join, every row from students_natural is retained in the result.

Step 4: payments
payments is the right table. When a matching value is found in the join column, the corresponding columns from payments are included in the result.

How the Join Works

Assume the two tables contain a common column such as a student identifier. DAX uses that common column to match rows between students_natural and payments.

This is the key behavior of a left outer join: rows from the left table are not removed just because a matching row does not exist in the right table.

2. Selecting Specific Columns from the Joined Result

Query

// Select specific columns from the result table

EVALUATE
    SELECTCOLUMNS(
        NATURALLEFTOUTERJOIN(
            students_natural,
            payments
        ), 
        [Name], 
        [Paid]
    )

Step-by-step explanation

Step 1: EVALUATE
The EVALUATE statement returns the final table produced by the expression.

Step 2: NATURALLEFTOUTERJOIN
First, DAX creates a joined table by combining students_natural with payments.

Step 3: SELECTCOLUMNS
The result of the join is then passed into SELECTCOLUMNS. This allows us to return only the columns required for the final output.

Step 4: [Name]
The [Name] column is selected from the joined result.

Step 5: [Paid]
The [Paid] column is also selected from the joined result.

Understanding the Query Flow

It is useful to read the query from the inside out:

1. NATURALLEFTOUTERJOIN combines the two tables.
2. The joined table becomes the input to SELECTCOLUMNS.
3. SELECTCOLUMNS keeps only [Name] and [Paid].
4. EVALUATE returns the final table.

students_natural
        +
    payments
        ↓
NATURALLEFTOUTERJOIN
        ↓
   Joined Table
        ↓
 SELECTCOLUMNS
   [Name], [Paid]
        ↓
     EVALUATE

What Will You Get?

The final query returns only the two selected columns:

The values above are only an illustration. Your actual result will depend on the data available in students_natural and payments.

Understanding NATURALLEFTOUTERJOIN vs SELECTCOLUMNS

Important Notes

1. The Left Table Controls Row Preservation

In a left outer join, all rows from the first table are preserved. In this example, students_natural is the left table.

2. Matching Columns Are Important

NATURALLEFTOUTERJOIN identifies the columns used for the natural join based on common column names and compatible data lineage. Therefore, the structure of the two tables matters when using this function.

3. Unmatched Right-Side Values Become Blank

If a row from students_natural does not have a matching row in payments, the row from the left table is still returned. Columns coming from the right table can contain BLANK.

4. SELECTCOLUMNS Creates the Final Projection

The second query does not return every column produced by the join. SELECTCOLUMNS projects the joined table into the requested [Name] and [Paid] columns.

EVALUATE
    SELECTCOLUMNS(
        NATURALLEFTOUTERJOIN(
            students_natural,
            payments
        ),
        [Name],
        [Paid]
    )

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist