Forum Discussion
Mapping between Join type and cardinalities in PowerBI
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.
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 -> LeftOuterI have the same results as you tested, I confirm you are right!
5 Replies
- v-diye-msft
Community Support
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?
- 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