Forum Discussion
ahuhn
7 years agoAdvocate I
full outer join in dax
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 gener...
Anonymous
7 years agoNot applicable
ahuhn but naturaleftouterjoin function gives only left join results right? if we need full outer join, is there any function?
khandu
7 years agoRegular Visitor
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.