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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ahuhn
Advocate I
Advocate I

naturalleftjoin DAX error at-least one common join column

I have two tables I am trying to perform a NATURALLEFTOUTERJOIN together based no the common column, 'name', but am receiving an error. 

 

'No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.'

 

My DAX formula is: MSTable = NATURALLEFTOUTERJOIN('Now','Before')

 

Picture1.png

 

Any suggestions as to why? The documentation (https://msdn.microsoft.com/en-us/query-bi/dax/naturalleftouterjoin-function-dax) says:

 

'an inner join of a table with another table. The 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.'

 

If I create a connection between 'Now' and 'Before', I get the following error:

 

'Column '' in Table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.'

 

Any suggestions why this occurs?

8 REPLIES 8
ngank03
Microsoft Employee
Microsoft Employee

I found the other way to do this without writting DAX : You can go to Modeling -> Merge Queries as new, choose join them as inner join. This will create a new table for you.image.png

ngank03_0-1637734088554.png

 

TimoRiikonen
Helper V
Helper V

Let's add couple of more requirements to @Chihiro's list:

1. Relationship between column(s) defining join must be set before the function can be used.

2. Related column must have different name between two tables being joined.

3. Natural joins don't work for DirectQueries. So then you need to use CrossJoin instead. Remember to still rename matching columns differently from each other.
4. DirectQuery must have less than a million rows.

Helpful! Thanks

Amit2019
Frequent Visitor

Hi ahuhn,

 

You can make typecasing explictly in Power Query for common column i.e. at the time of loading of data using M formula.

 

OR

 

Below is the solution that i can think  and suggest your. If NATURALJOIN is not able to match columns types for common column in "NOW" and "BEFORE" table, you need to make data type compatible explictly.

Below is the workaround -

 

Assuming that common column is "Name" and Relationshipt is in place.

 

 

NATURALINNERJOIN(

                                       SELECTCOLUMNS('NOW',"Name",NOW[name]&"",<add another name , column as required>) ,

                                       SELECTCOLUMNS( 'BEFORE',"Name",BEFORE[name]&"",<add another name, columns as requierd)

                                             )

 

Hope it should resolve this issue.

 

Thanks,

Amit Dhiman

Thank you, this was helpful to fix the error without having to rename the columns. 

One issue I had with this is that I was attempting to use a natural join on a temporary table within a measure and another table. I had to do all my joining as my first step, because once a temporary table was created, the relationship that the original tables had that the natural join would be performed on was lost.

 

Hope this helps anyone in a similar situation!

Anonymous
Not applicable

I'm having the same issue here.
I have a static timeseries table that looks like this Date, ID, Value
I need to "filter" it into 2 tables using the endpoints of a slicer.
SData & EData are the result of this filtering using CalculateTable.
Both get their respective data points & nothing else.
Then I need to use "full join" on both SData & EData using ID as the join point.
This is where I got tripped up.
Certainly, I could do this in SQL readily but I need to dynamic aspect of the above if it works.

Chihiro
Solution Sage
Solution Sage

You need to have columns that define joins named differently.

 

It is counter intuitive, but in order for the function to work.

 

1. Relationship between column(s) defining join must be set before the function can be used.

2. Related column must have different name between two tables being joined.

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.