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.
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.
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:
Let us understand these types better in more detail.
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.
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.
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.
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.
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.
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:
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.
There are several DAX functions that can be used to help model relationships.
The following are some of those functions: