Forum Discussion

AliceW's avatar
AliceW
Icon for Power Participant rankPower Participant
4 years ago
Solved

Natural Left Outer Join in DAX?

Hello everyone, This should be simple, but I can't find a solution, so I turn to you, kind strangers :0) I need to merge two tables using NATURALLEFTOUTERJOIN. I need this in DAX instead of PowerQu...
Need Help
  • PC2790's avatar
    PC2790
    4 years ago

    Try this:

    Left Outer = 
var SPLITTABLE =
SELECTCOLUMNS(Splits,"Opp ID",Splits[Opp ID]+0,"Split Owner", Splits[Split Owner])
var PRODTABLE =
selectcolumns(Products,"Opp ID", Products[Opp ID]+0,"Parent Product", Products[Parent Product])
return
NATURALLEFTOUTERJOIN(PRODTABLE,SPLITTABLE)

    Outcome as below:

     

PC2790's avatar
PC2790
Icon for Community Champion rankCommunity Champion
4 years ago

There are some considerations while using the function.

"There is no sort order guarantee for the results.
Columns being joined must have the same data type and the same name in both tables.
The columns considered for the join are those of the expanded table, not just the base table: two tables can be joined through common columns in related tables.
The columns used in the join condition that correspond to physical columns of the data model must also have the same data lineage; two columns with the same name and different data lineage generate an error.
Two columns with the same data lineage must have also the same full column name, which includes both table name and column name; otherwise, they are not matched for the join.
Strict comparison semantics are used during the join. There is no type coercion; for example, 1 does not equal 1.0."

Refer this for the same

AliceW's avatar
AliceW
Icon for Power Participant rankPower Participant
4 years ago

I have the same data type for the column I wish to use as a joiner... Not sure about the meaning of 'data lineage'...

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Try this:

    Left Outer = 
var SPLITTABLE =
SELECTCOLUMNS(Splits,"Opp ID",Splits[Opp ID]+0,"Split Owner", Splits[Split Owner])
var PRODTABLE =
selectcolumns(Products,"Opp ID", Products[Opp ID]+0,"Parent Product", Products[Parent Product])
return
NATURALLEFTOUTERJOIN(PRODTABLE,SPLITTABLE)

    Outcome as below:

     

    • AliceW's avatar
      AliceW
      Icon for Power Participant rankPower Participant
      4 years ago

      Thank you so much, PC! Not sure why that pesky +0 was needed to 'break the lineage', but it works!!

    • udayravi's avatar
      udayravi
      New Member
      3 years ago

      Hi,

      Suppose, Performed left outer join Table having one more column like Id.

      The Id Column should be join with more than one table. is it possible ?

      I believe it perform join with one table only.