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
ahuhn's avatar
ahuhn
Advocate I
7 years ago

Very odd error:

 

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

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.