Forum Discussion

vyacheslavg's avatar
vyacheslavg
Icon for Helper II rankHelper II
7 years ago
Solved

Help with data modeling (how to join two tables)

Hello colleagues,    could you please help with a basic (fundamental) understanding of how joins work in the PBI (Power BI) data model.   I have two related tables - parts and assemblies, which c...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi vyacheslavg 

    I make a test as below

    Sheet1

    index1 id1 id2
    1 1 1
    2 2 1
    3 1 2
    4 55 99

     

    Sheet2

    index2 id3
    1 1
    2 1
    3 2
    4  
    5 33

     

    create a new table

    Table = SUMMARIZE(NATURALLEFTOUTERJOIN(Sheet2,Sheet1),Sheet1[id1],Sheet1[id2],Sheet2[id3])

    id1

    id2

    id3

    1

    1

    1

    2

    1

    1

    1

    2

    2

    55

    99

    		  
       

    33

     

    Note, 

    NATURALLEFTOUTERJOIN(Sheet2,Sheet1)
    Sheet2->has 5 rows which the new table should have
    Sheet1->has 4 rows
     
    Best Regards
    Maggie
     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vyacheslavg's avatar
vyacheslavg
Icon for Helper II rankHelper II
7 years ago

I guess, my question boils down to a very simple question - how to do a full outer join in DAX?

Power query does exactly what i need.

I've tried NATURALLEFTOUTERJOIN. No, this is a left outer join.

I've tried CROSSJOIN. No, this is a cartesian multiplication of tables.

I watched all Ruth/Curbal videos, related to joins - everything in Power Query - no problems.

But I can't figure out the full outer join in DAX :smileyindifferent:

 

 

 

Power query, full outer - the needed result

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi vyacheslavg 

    I make a test as below

    Sheet1

    index1 id1 id2
    1 1 1
    2 2 1
    3 1 2
    4 55 99

     

    Sheet2

    index2 id3
    1 1
    2 1
    3 2
    4  
    5 33

     

    create a new table

    Table = SUMMARIZE(NATURALLEFTOUTERJOIN(Sheet2,Sheet1),Sheet1[id1],Sheet1[id2],Sheet2[id3])

    id1

    id2

    id3

    1

    1

    1

    2

    1

    1

    1

    2

    2

    55

    99

    		  
       

    33

     

    Note, 

    NATURALLEFTOUTERJOIN(Sheet2,Sheet1)
    Sheet2->has 5 rows which the new table should have
    Sheet1->has 4 rows
     
    Best Regards
    Maggie
     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.