Mapping between Join type and cardinalities in PowerBI

[Anonymous]

Hi,

can anyone share a internal link or explanation on how cardinalities and joins work in PowerBI. Is there a mapping between join types and cardinalities

example:

one to one is considered as a join type(for example: full outer join) and so on.

[v-diye-msft]

Hi @Anonymous 

For the cardinalities:

Many to One (*:1) - The most common, default type, which means the column in one table can have more than one instance of a value, and the other related table, often know as the Lookup table, has only one instance of a value.

One to One (1:1) - The column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value.

Many-to-many relationships: With composite models, you can establish many-to-many relationships between tables, which removes requirements for unique values in tables. It also removes previous workarounds, such as introducing new tables only to establish relationships. For more detailed information, see Relationships with a many-many cardinality.

For the join type:

Left Outer (All from first, matching from second)

Right Outer (all rows from second, matching from first)

Full Outer (all rows from both)

Inner (only matching rows)

Let Anti (rows only in first)

Right Anti (rows only in second)

You can refer to the much details here: https://radacad.com/choose-the-right-merge-join-type-in-power-bi

[Anonymous]

Hi @v-diye-msft ,

This did not answer my question.

Let me take an Example.

created 2 tables

Employee:

Department:

created a relationship in datamodel between Employee and Department and it is a many to one relationship:

Now during creation of a table  visual in report view

when I select  deptid(employee),empid,empname from Employee table and deptid(department),deptname the visual looks like below:

This looks like a left outer join.

similarly when the cardinality between Employee and Department is One to Many, then I see a Right outer join in the visual.

When cardinality is one to one, there is a full outer join and when cardinality is many to many, there is a inner join in the visual.

Is there such a mapping between cardinality and  type of join in datamodel(no mention of Query Editor here) of power BI?

[v-diye-msft]

Hi @Anonymous 

I figure that you mixed the join type in merging tables and creating relationships.  The join type is mostly mentioned when we merging tables, and it is exactly like what I posted before. it is operated in power query when you combine 2 tables into 1. also it does not quite matter what the relationship type is. 

But when you manage the relationship in the report interface, it is working as you saw. you can look through this article for better understanding: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

[Anonymous]

Hi @v-diye-msft ,

Thanks for the reply.

What we are trying to do is, instead of Merge to combine two tables, we want to use relationships to combine data from two tables.

Actually, In Manage Relationships pane, what we have given with is to specify the Cardinality between tables.
But, in Merge pane we have join type (like Inner, Left outer, Right Outer...etc), based on which the result changes.

1. Similar to join type in Merge pane, can we specify Cardinality in Manage Relationships pane to create different types of relationships? (Like, LeftOuter, Rightouter, Inner.. etc)

2. Is there any mapping or similarity between the Cardinaity in Manage Relationships pane and Join Type in Merge pane?

3. Could you please confirm our below observation is correct?

We did a small experiment with two tables (as mentioned in previous reply), by changing the Cardinality and checking the Result data.

Below is what we observed, from Cardinality and Result we get

many : many -> Inner 
one : one -> Full Outer 
one : many -> RightOuter 
many : one -> LeftOuter 
Thanks,
RajyaLaxmi

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Sorry for my late reply. 

1. Similar to join type in Merge pane, can we specify Cardinality in Manage Relationships pane to create different types of relationships? (Like, LeftOuter, Rightouter, Inner.. etc)

It's not able to specify the cardinality when you manage the relationship.

2. Is there any mapping or similarity between the Cardinality in Manage Relationships pane and Join Type in Merge pane?

Based on my research, we don't have the option to achieve this.

3. Could you please confirm our below observation is correct?

We did a small experiment with two tables (as mentioned in previous reply), by changing the Cardinality and checking the Result data.

Below is what we observed, from Cardinality and Result we get

many : many -> Inner 
one : one -> Full Outer 
one : many -> RightOuter 
many : one -> LeftOuter 

I have the same results as you tested, I confirm you are right!