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:

     

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.