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