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 20221IC - SynGroup100 20223Total debt (Group)500 20211IC - SynGroup400 20224Proj Exp Alloc800 20215Additional Costs50 20221IC - SynGroup900 TableB GroupIDGroupDetail 1Incurred Costs 2Cost group - Academia 3Total debt (Group) 4Projected Expenditure 5Additional Incurred Costs ExpectedOutput PostingYearGroupIDGroupDetailSpends 20221Incurred Costs1000 20222Cost group - Academia 0 20223Total debt (Group)500 20224Projected Expenditure800 20225Additional Incurred Costs0 20211Incurred Costs400 20212Cost group - Academia 0 20213Total debt (Group)0 20214Projected Expenditure0 20215Additional Incurred Costs50

Anonymous

Not applicable

4 years ago

Solved

Full outer join using calculated columns

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

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

Forum Discussion

Full outer join using calculated columns

3 Replies

Related content

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

Power Query or Calculated columns

IF calculated column

Dynamic Variable in Calculated Columns