Forum Discussion
How to join summarized tables?
I woul like to know how to join two tables that I get with SUMMIRIZE function. Tables doesn't have a direct relationship, they only connected by common dimensions.
For example, I have the code that doesn't work:
var table1 = SUMMARIZE(
FactTable1,
FactTable1[CustomerId],
FactTable1[DateId],
FactTable1[CategoryId],
FactTable1[Amount]
)
var table2 = SUMMARIZE(
FactTable2,
FactTable2[CustomerId],
FactTable2[DateId],
FactTable2[CategoryId],
FactTable2[AmountWithDifferentName]
)
return NATURALLEFTOUTERJOIN(table1, table2)And the execution of this code throws an error message: "No common join columns detected. The join function requires at-least one common join column."
6 Replies
- amitchandak
Super User
Anonymous , see if this can help
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
- AnonymousNot applicable
Hi Amit ,
I too got the same error with the same kind of scenario. Below is part of my code.
VAR __Table1 = SELECTCOLUMNS(__Table,"Date",fact_pn_transaction[Transaction Date],"x",[x])VAR __Table2 = FILTER(SUMMARIZE('Month Year','Month Year'[Date]),'Month Year'[Date]> __BeginDate && 'Month Year'[Date] <= __EndDate)VAR __Table3 = SELECTCOLUMNS(__Table2,"Date_c",'Month Year'[Date],"x_c",CONVERT(0,INTEGER))RETURN NATURALLEFTOUTERJOIN(__Table3,__Table1)Error: No common join columns detected. The join function 'NATURALLEFTJOIN' requires at-least one column join column.Any help would be much appreciated.Thanks
- adudani
Memorable Member
Anonymous ,
try changing the name to be a common name between the two columns in the tables to be joined.
NATURALLEFTOUTERJOIN function (DAX) - DAX | Microsoft Learn
- FreemanZ
Super User
hi Anonymous
are you expecting this?
UNION(Table1, Table2)
- AnonymousNot applicable
Hi
My Issue got resolved by using groupby DAX function.
Thanks,