topic Re: How to join summarized tables? in DAX Commands and Tips

How to join summarized tables?

Anonymous — Wed, 26 Aug 2020 15:01:02 GMT

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

Re: How to join summarized tables?

amitchandak — Wed, 26 Aug 2020 15:58:09 GMT

Anonymous , see if this can help

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

Re: How to join summarized tables?

Anonymous — Fri, 13 Jan 2023 18:25:58 GMT

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

Re: How to join summarized tables?

adudani — Fri, 13 Jan 2023 18:39:24 GMT

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

Re: How to join summarized tables?

Anonymous — Mon, 16 Jan 2023 09:59:47 GMT

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

Re: How to join summarized tables?

FreemanZ — Mon, 16 Jan 2023 13:56:26 GMT

hi Anonymous

are you expecting this?

UNION(Table1, Table2)

Re: How to join summarized tables?

Anonymous — Mon, 16 Jan 2023 18:23:31 GMT

My Issue got resolved by using groupby DAX function.

Thanks,