Relationships are established between tables to connect them via an attribute and the tables can be considered as one whole table for further process. However, in many cases, Power BI creates relationships on its own. In this article, we will learn more about creating table relationships in Power BI Desktop.
What are Table Relationships?
A relationship described between two or more tables via a common attribute is termed a table relationship. It is very crucial as it enables users to access data from two separate tables with ease.
Types of Table Relationships
There are four types of table relationships
- One-to-One (1:1): In One to One cardinality, one entity in A is related to one entity in B. For example, one student can belong to one class only.
- One-to-Many (1:*): In One to Many cardinality, one entity in A is related to many entity in B. For example, one student can engage in many activities.
- Many-to-One (*:1): In Many to One cardinality, many entity in A are related to one entity in B. For example, many students can belong to one class.
- Many-to-Many (*:*): In Many to Many cardinality, many entity in A are related to many entity in B. For example, many students have many hobbies.
Creating Table Relationships in Power BI Desktop
In order to create table relationship in Power BI Desktop, follow the following steps:
Create a table by clicking on “Enter data”.
Creating Table Relationships in Power BI Desktop
Insert the desired records in the table for instance make table student with attributes Student ID, Name, Class, Total Marks.
Table Relationships in Power BI Desktop
After inserting the records, click on load to save the table.
Create another table for instance Studentinfo with attributes Student ID, Name, Phone No., Address.
Insert records in Studentinfo.
Table Relationships in Power BI Desktop
Table Relationships in Power BI Desktop
Click on Manage relationships to establish relationship between tables.
Table Relationships in Power BI Desktop
Manage relationships dialog box appears which shows relationship between table.
Create Power BI relationships using AutoDetect
After clicking on manage relationships, dialog box shown below pops on the screen which tells there are no relationships defined yet.
Table Relationships in Power BI Desktop
Click on Autodetect and the relationships that have been detected automatically appears.
Table Relationships in Power BI Desktop
Table Relationships in Power BI Desktop
Create Power BI relationships manually
Click on New in Manage relationships to establish new relation.
Select Table name and the attribute of both the table to establish relationship. For instance, Select Name from both Student and Studentinfo table and establish the relationship.
Table Relationships in Power BI Desktop
The relationship established will be shown in Manage relationships.
Table Relationships in Power BI Desktop
Click on “Model view”
Table Relationships in Power BI Desktop
The relationship established will be shown in Model view. The relationship established between Student and Studentinfo via Student ID is shown below.
Table Relationships in Power BI Desktop
The relationship established between Student and Studentinfo via Name is shown below.
Table Relationships in Power BI Desktop
Cardinality
- One-to-One (1:1) : In One to One cardinality, one entity in A is related to one entity in B. For example, one student can belong to one class only.
- One-to-Many (1:*) : In One to Many cardinality, one entity in A is related to many entity in B. For example, one student can engage in many activities.
- Many-to-One (*:1) : In Many to One cardinality, many entity in A are related to one entity in B. For example, many students can belong to one class.
Table Relationships in Power BI Desktop
- Many-to-Many (*:*) : In Many to Many cardinality, many entity in A are related to many entity in B. For example, many students have many hobbies.
Cross filter direction
Cross filter considers the attributes/columns from both the tables that join them together and allows the user to tell the direction of filtering allowed.
- Single: It represents a single direction filter. In this filtering choices in connected tables work on the table where values are being aggregated.
- Both: It represents a bi-directional filter. In this, for filtering both tables connected are considered as one single table.
Identifying Key Fields for Relationships
A table relationship works by matching key fields.
- They can be a fields with same name in both the tables.
- Mostly, the primary key of one table acts as a foreign key in another table and helps in establishing a relationship between two tables.
Table Relationships in Power BI Desktop
- Primary key is the unique key which has unique values through out the table for eg. Student ID of a student.
Configuring and Establishing One-to-Many Relationships
Click on ‘Enter data’ and create a table Streams which represents Science and Commerce stream .
Table Relationships in Power BI Desktop
The table Stream will look like the table given below.
Table Relationships in Power BI Desktop
Click on ‘Manage relationships’ to establish a relationship between given tables.
Then establish a relationship between Stream and Student where class acts as a common entity. The relationship established is one to many as shown below.
Table Relationships in Power BI Desktop
The relationship established between Stream and Student is one to many relationship (1:*).
Table Relationships in Power BI Desktop
How to make relationship active?
User can make relationship Active in two ways:
Table Relationships in Power BI Desktop
- One can make relationship Active while creating it. In create relationship, after creating it user must click on “Make this relationship Active” to make relationship active.
In Manage relationships, one can make any relationship “Active” by clicking on checkbox under Active.
Table Relationships in Power BI Desktop
Creating table relationships in Power BI Desktop is a crucial step in building a robust data model that allows for effective analysis and visualization.