Why naturalinnerjoin dax not working

[huzefa_53]

VAR virtualTable = NATURALINNERJOIN(GROUPBY(filtered,'TT Players'[member_id], 'TT Players'[holes], "Rounds", COUNTAX(CURRENTGROUP(), 'TT Players'[holes])),'MM Members')

getting error no common column but member_id is common in both table and both have same data type why this is creating a issue

[v-rongtiep-msft]

Hi @huzefa_53 ,

What is your connection mode? 

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

• 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].

• Strict comparison semantics are used during join. There is no type coercion; for example, 1 does not equal 1.0.

• There is no sort order guarantee for the results.

• Columns being joined on must have the same data type in both tables.

I have also found a similar post, please refer to it to see if it helps you.

Many to many join not possible, "No common join column detected" 

If I have misunderstood your meaning, please provide your pbix file without privacy information (or some simple sample data) without privacy inforamtion and your desired output.

How to Get Your Question Answered Quickly

Best Regards

Community Support Team _ Polly

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

[huzefa_53]

I have made this DAX

var virtualTable = NATURALINNERJOIN( SELECTCOLUMNS( GROUPBY(filtered,'TT Players'[member_id], 'TT Players'[holes], "Rounds", COUNTAX(CURRENTGROUP(), 'TT Players'[holes])),"member_id",'TT Players'[member_id] & "","Rounds",[Rounds],"holes",[holes]) ,SELECTCOLUMNS('MM Members',"member_id",'MM Members'[member_id] & ""))

still didn't get the desired result

[SpartaBI]

@huzefa_53as they come from different tables, you need to break the data lineage of both of them.
Wrap your tables with SELECTCOLUMNS, keep the same names, but for the columns of the join write an expersion like [column] & ""

Showcase Report – Contoso By SpartaBI

      

[BummerBert69]

Fantastic tip - solved my issue as well 🙂

[huzefa_53]

@SpartaBI  Thanks For your answer,

If thats the case than there is no purpose of join because both selectcolumn are virtual table i can't drop columns from these virtual table i need to made relationship of one virtual table with static table that's how i will be able to achive drill down because my other visual use MM Member (member_id) and i want when i click the value of this dax row it drill down that particual member only that's why i need to create join

let me know if you need any more information

[SpartaBI]

@huzefa_53 You can always return the lineage after using treatas for the filteting purpose you need. 
I can't know if in general maybe you have a better and simple way to solve what you want. Need to see the full logic you want to create. 
Before that, try to start with returning the lineage after the join

[huzefa_53]

I can't understand what you mean by returning linage kindly can you write in the dax 

currently i have this dax

var virtualTable = NATURALINNERJOIN( SELECTCOLUMNS( GROUPBY(filtered,'TT Players'[member_id], 'TT Players'[holes], "Rounds", COUNTAX(CURRENTGROUP(), 'TT Players'[holes])),"member_id",'TT Players'[member_id] & "","Rounds",[Rounds],"holes",[holes]) ,SELECTCOLUMNS('MM Members',"member_id",'MM Members'[member_id] & ""))

the filtered is var declared kindly assume any table filtered out in there

[SpartaBI]

@huzefa_53 somthing like this:

virtualTable =
TREATAS(
	NATURALINNERJOIN (
	    SELECTCOLUMNS (
	        GROUPBY (
	            filtered,
	            'TT Players'[member_id],
	            'TT Players'[holes],
	            "@Rounds",
	                COUNTAX (
	                    CURRENTGROUP (),
	                    'TT Players'[holes]
	                )
	        ),
	        "@member_id",'TT Players'[member_id] & "",
	        "@Rounds", [Rounds],
	        "@holes", [holes]
	    ),
	    SELECTCOLUMNS (
	        'MM Members',
	        "@member_id",
	            'MM Members'[member_id] & ""
	    )
	),
	'MM Members'[member_id],
	'TT Players'[holes],
	.
	.
	.
)

the treat as takes the columns returned in the table in the first argument and then give them the lineage of the list of columns I pur after.

Watch this videos for reference:
https://www.youtube.com/watch?v=7kWeDqKRy2g

https://www.youtube.com/watch?v=6WU7Ze32Q3w

Showcase Report – Contoso By SpartaBI