What is a relationship and how does it affect Power Bi
Relationships in both life and Power Bi can be complicated. Relationships in Power Bi are necessary as they form a bond between different tables to accurately correlate and display data. There are various cardinalities used in these relationships and different types of filters that assist in displaying your data. Having the incorrect type of filtering or cardinality can cause Power Bi to show the wrong type of information.
There are three types of relationships:
– Many to One (* to 1)
– One to One (1:1) and
– Many-to-Many relationships
Many to One is the most common type. This cardinality indicates that there are multiple instances of one particular value in one table while the other table may only have one instance of the value.
In the below example, we can see that the customer table only has 1 instance of the Customer ID, while in the Order Table there can be multiple instances of the Customer ID. This is because the Customer ID is unique in the Customer Information table, while in the Order table, the Customer ID can appear multiple times, due to many orders
Another type of cardinality is One to One, this is when only one instance of value appears in each table, this type of relationship is usually seen in multiple smaller tables. In the below example there is only one instance of Customer ID in both tables as the Customer ID is unique to both tables.
The final type of cardinality is Many-to-Many, this type of cardinality is different from the other types as it does not need to have unique values in each of the tables to make the relationships possible. Prior to this relationship, a workaround had to be employed when multiple tables did not have unique values, an example of a workaround would have been to create other tables with unique values to form a bridge between them.
Cross filter direction
Besides the filters seen on the report editor page (Visual, Page, Drill-through and Report) there is another important filter that is used between tables. This is cross filter direction and “apply security filter in both directions”
When creating relationships in Power Bi, you first choose the tables and the columns that will form the relationships, the cardinality, then to make the relationship active and finally what direction will the filtering be in.
The difference between “Single” and “Both” in terms of cross-filtering is as follows:
Both filterings are used when multiple tables need to be treated as a single table. While single is when one of the table’s values are being aggregated.
The Both cross-filtering should be used when there are multiple look up tables connected to one central table (i.e. a Star Schema Configuration) while the Single cross-filtering is used when only one side of the table will have any filters/changes applied to it.
The cardinality/type of relationships, as well as the filtering involved between tables, is important in Power Bi to ensure that the information being presented is accurate.
For more information on this topic, please see the below links: