Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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!
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
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?
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
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
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
83 | |
82 | |
67 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |