cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ahuhn Regular Visitor
Regular Visitor

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?

2 REPLIES 2
Chihiro Established Member
Established Member

Re: naturalleftjoin DAX error at-least one common join column

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.

 

 

Amit2019 Frequent Visitor
Frequent Visitor

Re: naturalleftjoin DAX error at-least one common join column

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