Full Outer join Using DAX in Power BI

Thinking of full outer join in Power BI what comes first in your mind? How can we achieve full outer join in Power BI? Common Answer will be the “Use Merge Query” Option in Power Query Window.

However, I would like to tell you that we can use DAX to achieve Full Outer Join.

Full Outer Join = left Outer Join + right Anti Join

Customer Table:

Order Table:

Click on new table and write the below DAX:

DAX for Full Outer Join of Customer and Order Table:

FullOuterJoin = UNION(
    var CustomerleftouterjoinOrder = NATURALLEFTOUTERJOIN(CustomerData,RELATEDTABLE(Orders))
    return SELECTCOLUMNS(CustomerleftouterjoinOrder,
    "C_Id",CustomerData[CustomerID],
    "C_name",CustomerData[Name],
   "orderid",Orders[OrderID],
    "Amount",Orders[Amount]
    ),
    var customerunique = DISTINCT(CustomerData[CustomerID])
    return SELECTCOLUMNS(CALCULATETABLE(Orders,NOT(Orders[CustomerID] in customerunique)),
    "C_Id",Orders[CustomerID],
    "C_name"," ", 
    "orderid",Orders[OrderID],
    "Amount",Orders[Amount]
    )
    )

Result:

Hope this helps!! Thank You!!


Share Story :