How to join summarized tables?

[Anonymous]

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

[FreemanZ]

hi @Bhanu_VA 

are you expecting this?

UNION(Table1, Table2)

[Bhanu_VA]

Hi 

My Issue got resolved by using groupby DAX function.

Thanks,

[amitchandak]

@Anonymous , see if this can help

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Bhanu_VA]

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]

@Bhanu_VA ,

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

[Bhanu_VA]

Hi 

Thanks for the reply.

I did try by changing the name of the columns to same in both the cases.

Still no luck.

Error: The incompatible join column was dectected. 

Couldn't figure out why this error comes, even I have same datatypes,names for both the columns.

Thanks