cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahuhn Regular Visitor
Regular Visitor

full outer join in dax

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.

Picture1.png

 

I tried combinations of crossjoin and generate, but cannot find an appropriate solution. Is this possible?

 

Any suggestions welcome

6 REPLIES 6

Re: full outer join in dax

ahuhn Regular Visitor
Regular Visitor

Re: full outer join in dax

Very odd error:

 

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

Re: full outer join in dax

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

i'm not sure, but try this

Highlighted
ahuhn Regular Visitor
Regular Visitor

Re: full outer join in dax

Thanks for the suggestion.

 

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

 

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

 

succssfully

Re: full outer join in dax

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

khandu Visitor
Visitor

Re: full outer join in dax

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 -

1.PNG

2.PNG

and this is the output required.

3.PNG

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.