Help with data modeling (how to join two tables)

[vyacheslavg]

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_id	part_id
1	1
1	2
2	1
55	99

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

[vyacheslavg]

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 

Power query, full outer - the needed result

[v-juanli-msft · Accepted solution]

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.