MACHI's avatar
MACHI
Regular Visitor
4 years ago
Status:
Done

BUG? NATURALINNERJOIN NATURALLEFTOUTERJOIN

Hi

I've tried to use NATURALINNERJOIN and NATURALLEFTOUTERJOIN but their behaviour appears to me in contrast with the official documentation NATURALINNERJOIN function (DAX) - DAX | Microsoft Learn : "Tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned." Instead, the columns must have different names (and a relation between the tables is necessary). Example below:

 

Starting from the following model, I've tried to create a new calculated table "CLIENTI_CON_ORDINI":

CLIENTI_CON_ORDINI = NATURALINNERJOIN(Clienti, Ordini) . I obtained the following error when inputting the previous expression (same with NATURALLEFTOUTERJOIN):

Basically, it says that the field "IDCliente" already exsists. So two columns with the same name must NOT exist!
The same expression works in DAX Studio but the output table has two "IDCliente" fields! 
EVALUATE NATURALINNERJOIN(Clienti, Ordini)

If I rename one of the two "IDCliente" fields in the model, the problem is solved (that's the opposite of what the documentation says):

 

If I drop the relationship between Clienti and Ordini, i get this error (no common join column) even if i rename IDCliente2 as IDCliente!

 

Any comment?

 

 

 

Data Modeling

1 Comment

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi MACHI ,

     

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

    • Columns being joined on must have the same data type in both tables.
    • The names of the columns that define the relationship need to be different.

     

    Only columns from the same source table (have the same lineage) are joined on. For example, Products[ProductID], WebSales[ProductdID], StoreSales[ProductdID] with many-to-one relationships between WebSales and StoreSales and the Products table based on the ProductID column, WebSales and StoreSales tables are joined on [ProductID].

     

    You may check this to help you to have a better understand.

    NaturalInnerJoin and NaturalLeftOuterJoin DAX Functions - Power BI Docs

     

    Best Regards,
    Community Support Team _ Caitlyn

     

Completed Ideas