Power BI distinguishes itself from other database management systems through the concept of directionality in relationships. This aspect is pivotal for effective data filtering across multiple tables. When loading data, Power BI automatically identifies relationships by matching column names within the dataset. Additionally, users have the flexibility to manually modify these relationships using the Manage Relationships feature.

Consider a scenario where numerous tables are extracted from the Sales database, forming a semantic model. Power BI autonomously detects various relationships, but their interpretation is unclear. To ensure accuracy, users may utilize the Manage Relationships tool to align these relationships with the actual data structure.

Relationship

In Power BI, there are distinct relationship types, including the many-to-one (*:1) or one-to-many (1: *) relationship.

Many-to-One or One-to-Many Relationship:

1. • Defines a connection where multiple instances of a value in one column correspond to a single unique instance in another column.

     • Describes the directionality, commonly observed between fact and dimension tables.

     • Represents the prevalent directionality in Power BI, serving as the default when creating relationships automatically.

2. Example:

   • Illustrating a one-to-many relationship between the CountryName and Territory tables.

   • In this scenario, numerous territories can be linked to a single unique country.

3. One-to-one (1:1) relationship:

• Describes a relationship in which only one instance of a value is common between two tables.

• Requires unique values in both tables.

• Is not recommended because this relationship stores redundant information and suggests that the model is not designed correctly. It is better practice to combine the tables.

4. An example of a one-to-one relationship would be if you had products and product IDs in two different tables. Creating a one-to-one relationship is redundant and these two tables should be combined.

Many-to-many (.) relationship:

• Describes a relationship where many values are in common between two tables.

• Does not require unique values in either table in a relationship.

• Is not recommended; a lack of unique values introduces ambiguity and your users might not know which column of values is referring to what.

For instance, the following figure shows a many-to-many relationship between the Sales and Order tables on the OrderDate column because multiple sales can have multiple orders associated with them. Ambiguity is introduced because both tables can have the same order date.

Cross-filter direction

Data can be filtered on one or both sides of a relationship.

With a single cross-filter direction:

• Only one table in a relationship can be used to filter the data. For instance, Table 1 can be filtered by Table 2, but Table 2 cannot be filtered by Table 1.

• For a one-to-many or many-to-one relationship, the cross-filter direction will be from the "one" side, meaning that the filtering will occur in the table that has many values.

With both cross-filter directions or bi-directional cross-filtering:

• One table in a relationship can be used to filter the other. For instance, a dimension table can be filtered through the fact table, and the fact tables can be filtered through the dimension table.

• You might have lower performance when using bi-directional cross-filtering with many-to-many relationships.

A word of caution regarding bi-directional cross-filtering: You should not enable bi-directional cross-filtering relationships unless you fully understand the ramifications of doing so. Enabling it can lead to ambiguity, over-sampling, unexpected results, and potential performance degradation.

Cardinality and cross-filter direction

In one-to-one relationships, the sole option is bi-directional cross-filtering, allowing data filtration on either side to yield a distinct value. For example, filtering on a Product ID retrieves a single Product, and vice versa.

For many-to-many relationships, bi-directional cross-filtering offers the choice to filter in a single or both directions. However, the complexity intensifies due to multiple paths between tables, leading to potential ambiguity. Creating measures, calculations, or filters may lead to unintended consequences as the Power BI engine selects a relationship when applying the filter. This cautionary note extends to bi-directional relationships, emphasizing the need for prudence in their usage.

For this reason, many-to-many relationships and/or bi-directional relationships are complicated. Unless you are certain what your data looks like when aggregated, these types of open-ended relationships with multiple filtering directions can introduce multiple paths through the data.

Create many-to-many relationships

Consider the scenario where you are tasked with building a visual that examines budgets for customers and accounts. You can have multiple customers on the same account and multiple accounts with the same customer, so you know that you need to create a many-to-many relationship.

To create this relationship, go to Manage Relationships \> New. In the resulting window, create a relationship between the Customer ID column in CustomerTable and AccountTable. The relationship is set to many-to-many, and the filter type is in both directions. Immediately, you will be warned that you should only use this type of relationship if it is expected that neither column will have unique values because you might get unexpected values. Because you want to filter in both directions, choose bi-directional cross-filtering.

Select OK. You have now successfully created a many-to-many relationship.

SWETA SARANGI

2-3-2024