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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

0

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":

MACHI_0-1664124793899.png

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

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)

MACHI_2-1664125812334.png

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):

MACHI_0-1664132118209.png

 

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

MACHI_1-1664132501124.png
MACHI_2-1664132972522.png

 

Any comment?

 

 

 

Status: Delivered

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

 

Comments
v-xiaoyan-msft
Community Support
Status changed to: Delivered

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