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".
👁 1 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.
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.
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.
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.
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.
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.
Creating table relationships in Power BI Desktop is a crucial step in building a robust data model that allows for effective analysis and visualization.
