Relationships in Power BI give a lucid picture of how the multiple and independent tables are related to each other. No doubt, relationships in Power BI are a mystery for beginners. But this blog, will for sure solve your mystery and help you to manually create a many-to-many relationship in Power BI.

So, without wasting time, let’s see the basics first. That is, what are relationships in Power BI, what is the cardinality of relationships and what are their types?

What are the relationships in Power BI?

Relationships in Power BI are a way of relating to two or more tables. To be precise, while modelling relationships, we create a connection from a column of one table to a column in another table.

Why do we need to build relationships in Power BI?

In Power BI, when working with data models covering data from multiple tables, we cannot visualize the data from all these tables concurrently. In this situation, relationships help to visualize data from multiple tables discreetly. So, connecting the tables allows them to function together by slicing and dicing the data and allowing visualizations.
To clarify your understanding, in relationships between tables, we can treat those tables as a single table and work on data in them. In other words, relationships provide freedom from the time-consuming process of flattening multiple tables into one. Further, this enables the precise calculation of the results and displaying the correct information in your reports.

The other concept to grasp, related to relationships, is relationship cardinality.

What is the Cardinality of relationships?

In modelling relationships, cardinality is a way of defining relationships. To clarify, it donates the characteristics of data in the columns being connected in a relationship. Also, relationships are represented as “_to _”. Explicitly, this format means “from” and “to”. If there is “one” or “1” on any one or both sides of “to” it represents unique values in the column or columns. And if there is “many” or “*” on any one or both sides of the “to”, it represents duplicate values in the column or columns.

Types of Cardinalities in relationships

There are four cardinality-type options. They are as follows.
1. One-to-many (1:*)
2. Many-to-one (*:1)
3. One-to-one (1:1)
4. Many-to-many (*:*)

1.      What are One-to-many relationships in Power BI?

This cardinality defines a relationship in which one of the tables contains unique values per each row for the relationship field, and the other one has multiple values.

2.      What are Many-to-one relationships in Power BI?

This cardinality is the opposite of “one-to-many” in just direction while carrying the same meaning.

3.      What are One-to-one relationships?

This type of Cardinality defines a relationship when you have unique values in both tables per column.

4.      What are Many-to-many relationships?

Many-to-many relation occurs when columns in both tables have duplicate values. This is a rare cardinality type with complex modelling.

Let’s move towards “how to manually create many-to-many relationships in Power BI?”
Usually, Power BI Desktop automatically creates relationships, which excludes the necessity to create those relations manually. However, in the case of Power BI being unable to determine the existence of a relationship between tables with certainty, we have to create the relationship manually. So, here we go.

How do manually create and manage many-to-many relationships in Power BI?

Step 1

Get the Source Excel sheet from the drive and Load tables. In our case, the Excel sheet is “OrdersShipments” and the tables are “Orders” and “Shipments”

Step 2

Go into “Data View” to check if all the data is loaded correctly.

Step 3

Our Many-to-Many relationship is between the CustomerID columns of both tables.

However, first, we need to see whether Power BI has made a relationship on its own or not. For this, Go to “Model View”. In our case, Power BI has created a one-one relationship on its own between the OrderID column of each table. So, the next step will be managing the relationship. For this, we first delete that relationship to make our desired many-many relationship.

Step 4

Click on the “More Option” OR “Three dots” of any table in which you want to create that relationship. Then select “Manage Relationships” in the drop-down menu and click on “New” in Manage Relationships Tab.

Step 5

A new tab of “Create Relationships” will open. Click on the drop-down and select the first table. In our case, the first table is of “Orders,”. After selecting the first table, click on the column based on which you want to make a many-many relationship. In our case, it’s “ColumnID.” After the first table, select the second table and its respective column. In our case, “Shipments” is the table and “ColumnID” is the column.
After selecting Tables and respective columns, see the cardinality that says, “Many to many” and then click Ok.

A Many-to-Many relationship has been created.

The issue with many-to-many relationships

Many-to-many relationships lead to uncertainty due to data redundancy, insertion or deletion and cause updating problems. Therefore, many-to-many relationships are discouraged to create.

To sum Up

“Relationships in Power BI” is a freedom from the time-consuming process of flattening multiple tables into one. Further, they give you a lucid view of “how multiple and independent” tables are related to each other in just a few clicks. We hope that this blog has enhanced your knowledge of Relationships and “how you can manually create many-to-many relationships in Power BI?”.
Before wrapping up, We hope you got your answers. Ciao!