Power BI: Relationships between tables

• admin
• March 26, 2022

In this article, we will understand what relationships really mean when it comes to Power BI. We will know more about the types of relationships and how one can differentiate them.

Without a clear perspective of data links, it is impossible to extract much insight from data. They can, however, get complex at times. Here’s when Power BI comes in handy.

What Are Relationships In Power BI?

Relationships in Power BI indicate the link between two or more tables. They demonstrate how these tables connect to one another in a straightforward manner. In power bi models, you can import tables in a model and define relationships between them. A model relationship is a link between a column in one table and a column in another table.

Cardinality

Every model should have a cardinality type. They are mainly of 4 types. The most common ones are one to many and many to one.

Four types of relationships exist between database tables:

• One to Many
• Many to One
• One to One
• Many to Many

Let us understand these types better in more detail.

One to Many

When one item from the first table is related to several items from the second table, a one to many relationship is formed. In other words, this is a connection between single instance of one entity and multiple instances of another entity. This type of relationship focuses on the fundamental parent-child relation. In this case, the kid cannot have more than one parent record, but the parent can have a single child record, many child records, or no child records at all.

Many to One

When many items from the first table is related to only one item from the second table, a many to one relationship is formed. In other words, This is a connection between multiple instances of one entity and one instance of another entity. Let us assume the opposite here where the child can have many parents but the parent can have only one child.

When dealing with one to many and many to one cardinality types, you should choose the one that corresponds to the order in which you relate the columns.

One to One

In a one to one relationship, both columns have distinct values. When only one item from the first table is related to only one item from the second table, a one to one relationship is formed. In other words, This is a connection between one instance of one entity and one instance of another entity. This is one of the uncommon cardinality type, it is rarely used or preferred upon other cardinality types.

Many to Many

When both the columns have duplicate values and various items from the first table is related to several items from the second table, a many to many relationship is formed. In other words, This is a connection between multiple instances of one entity and multiple instances of another entity. This cardinality type is also rarely used or preferred upon other cardinality types.

Cross Filter Options

For each model relationship, a cross filter direction must be specified. The term “single cross filter direction” refers to a single direction, whereas “both” refers to both directions, or bi-directionality. Bidirectional interactions can have an adverse effect on the productivity. Furthermore, attempting to create a bi-directional relationship may result in unclear filter transmission paths. The cross filter options are dependent on and rely on the cardinality forms.

For example, for cardinality types of one to many or many to one, you can pick both “single” and “both” cross filter options. For one to one cardinality type, the cross filter direction is always from both the tables. Therefore, you can use the “both” cross filter option for it. For the cardinality type of many to many relationship, the cross filter direction can come from either one of the tables or both tables.

Assume Referential Integrity

Only one to many and one to one relationships between two DirectQuery storage mode tables based on the same data source are supported by the assume referential integrity property. This property enables you to do more efficient searches on your data source. Only when DirectQuery is used is this property available. When this option is set, queries on the data source can use INNER JOIN statements rather than OUTER JOIN statements. Doing so improves query performance.

The following are some assumptions for using assume referential integrity:

• Firstly, the data which is in the From column in the relationship can never be blank or null.
• Secondly, every value in the From column is always going to have a corresponding value to it in the To column.

If there happens to be a lack of referential integrity in a database, relational databases will end up returning incomplete data. The worst-case scenario is that this occurs without any notice of an error.

DAX

There are several DAX functions that can be used to help model relationships.

The following are some of those functions:

• RELATED: This function requires that the current table and the table with relevant information are linked. All you have to do is specify the column that holds the data you want, and this function will get the value from the chosen column in the related table for you. It does this by relying on a pre-existing many-to-one relationship. Therefore, it is important for a relationship to exist to avoid getting any error. The RELATED function gets the value from “one” side of the equation. Its syntax is: RELATED(<column>)
• RELATEDTABLE: This function basically modifies the nature of the data filtering and examines the expression in the new environment after the modification. The RELATEDTABLE function gets a table of rows from the “many” side. Its syntax is: RELATEDTABLE(<tableName>)
• USERELATIONSHIP: This function defines the relationship that will be used in a computation. It’s syntax is: USERELATIONSHIP(,). You can input any 2 columns and there will exist a relationship between them. This function requires the user to use a specified model relationship that is currently not active. Despite the fact that it does not yield a value, it allows us to calculate the connection we’ve decided to create between two columns.
• CROSSFILTER: We have already discussed about this in detail. It changes or modifies the cross filter direction of any relationship, whether one to many or many to many, and so on.