Full Outer Join in DAX in PowerBI

  10 Jul 2019
  powerbi

Full Outer Join

According to Wikipedia -

“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

Full Outer Join     =    Left Outer Join     +     Anti Right Join


To begin with, we have Department table as -

DepIDDep Name
01Marketing
12HR
24Finance
35Operations


and Employee tables as -

Emp IdNameIncomeDepID
01Shivani2001
12Rob1331
23Chris1902
34Tom2002
45Ria1203


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:
DepIDDep NameEmp IdNameIncome
01Marketing1Shivani200
11Marketing2Rob133
22HR3Chris190
32HR4Tom200
435Ria120
54Finance
65Operations

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

Related Posts