Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 j...
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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!

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
6 years ago

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