I am trying to do a full outer join in DAX (not in PQ)
I have two tables, T1 and T2, and am trying to make a merged table, M, as shown below.
I tried combinations of crossjoin and generate, but cannot find an appropriate solution. Is this possible?
Any suggestions welcome
Very odd error:
No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.
new table = naturalleftouterjoin( selectcolumns(T1;"name";[name];"statusBefore";[statusBefore]); selectcolumns(T2;"name";[name];"statusToday";[statusToday]))
i'm not sure, but try this
Thanks for the suggestion.
I renamed name to name1, made a relationship between the two tables, and ran
Table = NATURALLEFTOUTERJOIN('Now','Before')
@ahuhn but naturaleftouterjoin function gives only left join results right? if we need full outer join, is there any function?
I was not able to find a function, but found an approach -
Full outer join = Left Join + Right Anti Join.
For example -
I have two tables -
and this is the output required.
This can be achieved by -
FullOuterJoin = UNION( var DepartmentLeftOuterJoinEmp = NATURALLEFTOUTERJOIN(Department,RELATEDTABLE(Emp)) return SELECTCOLUMNS(DepartmentLeftOuterJoinEmp, "DepID", Department[DepID], "EmpID", [Emp Id], "Income", [Income], "Name",[Name], "DepName",[Dep Name] ), var DepartmentUniqueIds = DISTINCT(Department[DepID]) return SELECTCOLUMNS(CALCULATETABLE(Emp, NOT(Emp[DepID] in DepartmentUniqueIds)), "DepID", [DepID], "EmpID", [Emp Id], "Income", [Income], "Name",[Name], "DepName"," " ) )
the first table is left outer join and the other one is the right anti join, ie. those which is only present in the right table.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.