full outer join in dax

[ahuhn]

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

[petrovnikitamai]

try to use NATURALLEFTOUTERJOIN(T1;T2)

[ahuhn]

Very odd error:

No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.

[petrovnikitamai]

new table = 
naturalleftouterjoin(
selectcolumns(T1;"name";[name];"statusBefore";[statusBefore]);
selectcolumns(T2;"name";[name];"statusToday";[statusToday]))

i'm not sure, but try this

[ahuhn]

Thanks for the suggestion.

I renamed name to name1, made a relationship between the two tables, and ran

Table = NATURALLEFTOUTERJOIN('Now','Before')

succssfully

[Anonymous]

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

[khandu]

I was not able to find a function, but found an approach - 

Full outer join  = Left Join + Right Anti Join.

For example - 

I have two tables -

and this is the output required.

This can be achieved by - 

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.