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 consist of parts.

 

Parts table

 

part_id
1
2

33

 

Assemblies table

 

assembly_idpart_id
11
12
21
5599

 

I load and connect tables in Power BI, using part_id to define a relationship.

 

Then I create two very basic pivot tables for analysis:

1) to calculate the count of parts in assemblies and

2) the count of assemblies "in" parts.

 

I don't exactly understand the result I see, though. What I want to see is what was done in another (similar) BI software, but which uses full outer join (kind of, they use a different terminology for relationships) by default.

 

It looks, like Power BI uses only inner join to relate tables in the relationship screen? Is it possible to define left or right outer join in the relationship view? Or maybe DAX is required? I can't use the Power query or SQL or Pandas, where I can define the outer or negative joins -   because I need these datatables to be in a memory (RAM) for faster analysis.

 

 

 

 

 

 

Screenshot from another software, not PBIScreenshot from PBI

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.

2 Replies

  • 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.