Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
tchiang7
Frequent Visitor

NATURALLEFTOUTERJOIN not finding common join columns

I have three tables.

1. One is directQuery table "QuantifiedSelves Welln" which has direct query data from SQL database,

2. One is custom "CalendarDate" table created with Calendar() function -- CalendarDate = CALENDAR(DATE(2024,9,1), TODAY())

3. One is custom "CalendarMonth" table created based on "CalendarDate" table (see 2.) -- 

CalendarMonth = DISTINCT(CalendarDate[CalendarYearMonth])
 
There is a relation between "QuantifiedSelves Welln" table and "CalendarMonth". See below for the relation definition.
tchiang7_0-1731617705440.pngtchiang7_1-1731618108577.png

 

I want to left outer join "CalendarMonth" and  "QuantifiedSelves Welln" table together, so I can see aggregated values per CalendarMonth. If no values in a CalendarMonth, then aggregate value is null.

I tested NATURALLEFTOUTERJOIN() in DAX view (see below). Notice that, if comment out NATURALLEFTOUTERJOIN() line, then other two "EVALUATE" sections return correct results. It proves that two tables has correct values. Any problem I have???

tchiang7_2-1731618488745.png

 

2 ACCEPTED SOLUTIONS
AntrikshSharma
Super User
Super User

@tchiang7 You can join those tables by breaking the lineage for numeric columns use  +0 for text use & ""

AntrikshSharma_0-1731655872620.png

Join Sales & Product = 
VAR SalesTable = 
    SELECTCOLUMNS ( 
        Sales, 
        "ProductKey", Sales[ProductKey] + 0, 
        Sales[Quantity], Sales[Net Price] 
    )
VAR ProductsTable = 
    SELECTCOLUMNS ( 
        'Product', 
        "ProductKey", 'Product'[ProductKey] + 0, 
        'Product'[Brand], 'Product'[Color] 
    )
VAR Result = 
    NATURALLEFTOUTERJOIN ( SalesTable, ProductsTable )
RETURN
    Result

 

View solution in original post

There is another way of solving this, since JOIN functions expect same column names and same lineage but 2 Key column from different tables do not have the same lineage, even if they are connected through a relationship.

 

In this scenario adding 0 or "" disconnects their lineage from the model and they both act as columns that don't exists in the model so JOIN functions work fine.

 

However to make the lineage of both columns same we can use TREATAS which actually Treats one or more columns as the columns of some other tables.

 

Join Sales & Products TREATAS = 
VAR SalesTable = 
    SELECTCOLUMNS ( 
        Sales, 
        Sales[ProductKey], Sales[Quantity], Sales[Net Price] 
    )
VAR ProductsTable = 
    TREATAS ( 
        SELECTCOLUMNS ( 
            'Products', 
            'Products'[ProductKey], 'Products'[Brand], 'Products'[Color]
        ),
        Sales[ProductKey], Products[Brand], Products[Color] 
    ) 
VAR Result = 
    NATURALLEFTOUTERJOIN ( SalesTable, ProductsTable )
RETURN
    Result

 

So for the duration of the calculation Products[ProductKey] will be treated as Sales[ProductKey].

View solution in original post

4 REPLIES 4
tchiang7
Frequent Visitor

Thank you for the answer. It is working. 

AntrikshSharma
Super User
Super User

@tchiang7 You can join those tables by breaking the lineage for numeric columns use  +0 for text use & ""

AntrikshSharma_0-1731655872620.png

Join Sales & Product = 
VAR SalesTable = 
    SELECTCOLUMNS ( 
        Sales, 
        "ProductKey", Sales[ProductKey] + 0, 
        Sales[Quantity], Sales[Net Price] 
    )
VAR ProductsTable = 
    SELECTCOLUMNS ( 
        'Product', 
        "ProductKey", 'Product'[ProductKey] + 0, 
        'Product'[Brand], 'Product'[Color] 
    )
VAR Result = 
    NATURALLEFTOUTERJOIN ( SalesTable, ProductsTable )
RETURN
    Result

 

There is another way of solving this, since JOIN functions expect same column names and same lineage but 2 Key column from different tables do not have the same lineage, even if they are connected through a relationship.

 

In this scenario adding 0 or "" disconnects their lineage from the model and they both act as columns that don't exists in the model so JOIN functions work fine.

 

However to make the lineage of both columns same we can use TREATAS which actually Treats one or more columns as the columns of some other tables.

 

Join Sales & Products TREATAS = 
VAR SalesTable = 
    SELECTCOLUMNS ( 
        Sales, 
        Sales[ProductKey], Sales[Quantity], Sales[Net Price] 
    )
VAR ProductsTable = 
    TREATAS ( 
        SELECTCOLUMNS ( 
            'Products', 
            'Products'[ProductKey], 'Products'[Brand], 'Products'[Color]
        ),
        Sales[ProductKey], Products[Brand], Products[Color] 
    ) 
VAR Result = 
    NATURALLEFTOUTERJOIN ( SalesTable, ProductsTable )
RETURN
    Result

 

So for the duration of the calculation Products[ProductKey] will be treated as Sales[ProductKey].

v-tianyich-msft
Community Support
Community Support

Hi @tchiang7 ,

 

It seems to be fine, please check again for the following limitations:
Columns being joined must have the same data type and the same name in both tables.
The columns considered for the join are those of the expanded table, not just the base table: two tables can be joined through common columns in related tables.
The columns used in the join condition that correspond to physical columns of the data model must also have the same data lineage; two columns with the same name and different data lineage generate an error.
Two columns with the same data lineage must have also the same full column name, which includes both table name and column name; otherwise, they are not matched for the join.

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.