Full outer join using calculated columns

[Anonymous]

Hello Folks,

AFAIK, we cannot Join tables using calculated columns. But, I am now in a tricky situation that,

I have TableA and TableB. 

TableA has calculated columns( NOT custom columns.). These calculated columns are created at PowerBI level using DAX but not in PQ. On the other hand, I have TableB which I would like to join with. Could you provide any idea/approach to deal this.

In the below illustration, TableA and TableB should be joined. In the output table, ALL the rows from TableB should be displayed with the associated SPENDS data from TableA. Also, this should happen for both the years 2021 and 2020 and  GROUPBY(Year,Row) SUM(Spends)

TableA
	PostingYear (calculated column)	GroupID (calculated column)	GroupDetail (calculated column)	ApprovedSpend
	2022	1	IC - SynGroup	100
	2022	3	Total debt (Group)	500
	2021	1	IC - SynGroup	400
	2022	4	Proj Exp Alloc	800
	2021	5	Additional Costs	50
	2022	1	IC - SynGroup	900

TableB
	GroupID	GroupDetail
	1	Incurred Costs
	2	Cost group - Academia
	3	Total debt (Group)
	4	Projected Expenditure
	5	Additional Incurred Costs

ExpectedOutput
	PostingYear	GroupID	GroupDetail	Spends
	2022	1	Incurred Costs	1000
	2022	2	Cost group - Academia	0
	2022	3	Total debt (Group)	500
	2022	4	Projected Expenditure	800
	2022	5	Additional Incurred Costs	0
	2021	1	Incurred Costs	400
	2021	2	Cost group - Academia	0
	2021	3	Total debt (Group)	0
	2021	4	Projected Expenditure	0
	2021	5	Additional Incurred Costs	50

[Anonymous · Accepted solution]

Hi @Anonymous ,

You could get the result by creating a measure.

Measure = 
var _value = CALCULATE(SUM(TableA[ApprovedSpend]),FILTER(ALLSELECTED(TableA),TableA[PostingYear]=SELECTEDVALUE(TableA[PostingYear])&&TableA[GroupID]=SELECTEDVALUE(TableB[GroupID])))
return
_value+0

Best Regards,

Jay

[amitchandak]

@Anonymous , rename the column of Table 2 or use selectcolumns in next code to change name. I am assuming rename appending 1

Addcolumns(

crossjoin(Table1, Table2) , "New Spend", if( [GroupID] = [GroupID 1], [Spend], 0) )

Crossjoin does not take the same column name

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

[Anonymous]

@amitchandak 

Thanks for the help!