Full Outer Join in DAX in PowerBI

Full Outer Join

According to Wikipedia{:target="_blank"} -

“Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where rows in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those rows that do match, a single row will be produced in the result set (containing columns populated from both tables).”

So, the Full Outer Join can be acheived by creating -

• Left Outer Join
• Right Anti Join

    =          +     

To begin with, we have Department table as -

The two tables relates on DepID column with one to many relatioship between Department to Employee table.

The DAX query for the same in PowerBI can be written as -

FullOuterJoin = UNION(

    var DepartmentLeftOuterJoinEmp = NATURALLEFTOUTERJOIN(Department,RELATEDTABLE(Employee))
    return SELECTCOLUMNS(DepartmentLeftOuterJoinEmp,
        "DepID", Department[DepID],
        "EmpID", [Emp Id],
        "Income", [Income],
        "Name",[Name],
        "DepName",[Dep Name]
    ),

    var DepartmentUniqueIds = DISTINCT(Department[DepID])
    return SELECTCOLUMNS(CALCULATETABLE(Employee, NOT(Employee[DepID] in DepartmentUniqueIds)),
        "DepID", [DepID],
        "EmpID", [Emp Id],
        "Income", [Income],
        "Name",[Name],
        "DepName"," "
        )
)

Output:

Note:

• Another way to solve the same can be: Left Outer Join + Right Outer Join - Inner Join
  This can be achived in DAX in PowerBI by using: Distinct(Union(LeftOuterJoin,RightOuterJoin))