Forum Discussion

vyacheslavg's avatar
vyacheslavg
Helper 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
Helper 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
Community 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.