Table Relationships in Power BI

As you all know relationships are important within the tables, without the proper relationships between tables we won’t be able to get meaningful insights or the related information from them.

Here I have used a normal image for the table relationships, where we can see that in power BI the A marked tab shows us the view of the tables and B mark as the relationships between the tables.

The great thing about Power BI is that it auto-detects the relationships in the database and also allows us to create relationships.

Types of Relationships

As we all know that Relationships play an important role in databases it is a medium where the tables are connected with each other.

One to One:

A row in table A can have only one matching row in table B, and vice versa.

This type of relationship is not much implemented and when it is used it is used for some specific purposes like security, it is one of the use-cases which can be implemented.

One to Many:

This is the most common relationship type. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.

Many-to-Many:

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa.

A many-to-many relationship could be thought of as two one-to-many relationships, linked by an intermediary table.

The intermediary table is typically referred to as a “junction table” (also as a “cross-reference table”). This table is used to link the other two tables together.

One-One:

In a one-to-one relationship, one record in a table is associated with one and only one record in another table. For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person.

Why Relationships?

• It helps us to maintain our database in an efficient manner.
• We don’t require to assemble all the data in one table.
• We don’t have to perform joins between the tables if there is a relationship made between them in context to Power BI.

Let’s talk about the relationships in Power BI!

We can make any relationships between tables in Power BI nevertheless if they are functional or illogical.

Table 1 has no relationship with Tabe 2.

Step 1: To create the relationship we have to open the Manage relationships tab.

Step 2: Select two tables for the connection and the column from which we have to make the connection the advantage we get is that we do not require the 2 tables to have the same Column name but it should have the same Data Type.

We can change the cardinality(relationship) whenever we want.

With bidirectional cross-filtering, report creators and data modellers now have more control over how they can apply filters when working with related tables. Bidirectional cross-filtering enables them to apply filters on both sides of a table relationship.

Step 3: Click on the Ok when done and then it will reflect in the relationship tab of the database model.

The Arrow states that we can filter from both direction Table 1 and Table 2.

Tip: Avoid making Many-Many relation it can result in duplicate values.