Forum Discussion

ahuhn's avatar
ahuhn
Advocate I
7 years ago

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.   I tried combinations of crossjoin and gener...
Need Help
petrovnikitamai's avatar
petrovnikitamai
Resolver V
7 years ago
new table = 
naturalleftouterjoin(
selectcolumns(T1;"name";[name];"statusBefore";[statusBefore]);
selectcolumns(T2;"name";[name];"statusToday";[statusToday]))

i'm not sure, but try this

ahuhn's avatar
ahuhn
Advocate I
7 years ago

Thanks for the suggestion.

 

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

 

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

 

succssfully

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    • khandu's avatar
      khandu
      Regular Visitor
      7 years ago

      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.