full outer join in dax

Question

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 generate, but cannot find an appropriate solution. Is this possible?

Any suggestions welcome

khandu · Answer

I was not able to find a function, but found an approach -&nbsp;Full outer join&nbsp; = Left Join + Right Anti Join.&nbsp;For example -&nbsp;I have two tables -and this is the output required.This can be achieved by -&nbsp;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.

ahuhn · Answer

Thanks for the suggestion.&nbsp;I renamed name to name1, made a relationship between the two tables, and ran&nbsp;Table = NATURALLEFTOUTERJOIN('Now','Before')&nbsp;succssfully

anonymous · Answer

ahuhn&nbsp; but naturaleftouterjoin function gives only left join results right?&nbsp; if we need full outer join, is there any function?

petrovnikitamai · Answer

try to use&nbsp;NATURALLEFTOUTERJOIN(T1;T2)

ahuhn · Answer

Very odd error:&nbsp;No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.

Forum Discussion

full outer join in dax

6 Replies

Related content

Full Outer Join Handling Null Values in common fields

merge two table with full outer join values

Full outer join with three tables

Left Outer Join through Relation ship joining

Dax: Full Outer Join so all rows from both tables are kept