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!!
Related posts:
From Multiple Data Sources to Azure Databricks: A Zero-Downtime Power BI Migration for Future AI Rea...
Turning Lead Chaos into Clarity: How a Massachusetts-Based Manufacturing Business Transformed Its Sa...
How Project-Based Profitability Reporting Turns Dynamics 365 PSA Data into Better Business Decisions
