Define a relationship’s cardinality and cross-filter direction – Model the data

2024-03-03
No Comments

Define a relationship’s cardinality and cross-filter direction

In the previous section, we looked at how you create relationships between tables. In this section, we review the concepts of cardinality and cross-filter direction of relationships.

You can edit a relationship by double-clicking it in the Model view. For example, in Figure 2-4 you can see the options for one of the relationships between the Sale and Date tables.

FIGURE 2-4 Relationship options

In the relationship options, you can select tables from drop-down lists. You get a preview of each table, from which you can select a column that will be part of a relationship. Unlike in the Merge operation in Power Query, only one column from each table can be part of a relationship.

The Make this relationship active check box determines whether the relationship is active. Between two tables, there can be no more than one active relationship.

When you’re using DirectQuery, the Assume referential integrity option is available, and it can improve query performance in certain cases.

Note Assume Referential Integrity

There are some requirements that data must meet for the Assume referential integrity option to work properly. For advanced details on this feature, including the requirements and implications of not meeting the requirements with this option set, see “Apply the Assume Referential Integrity setting in Power BI Desktop” at https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-assume-referential-integrity.

Two options are worth reviewing in more detail: Cardinality and Cross filter direction.

Cardinality

Depending on the selected tables and columns, you can select one of the following options:

  • Many to one
  • One to one
  • One to many
  • Many to many

Many to one and One to many are the same kind of relationship, and they differ only in the order in which the tables are listed. “Many” means that a key may appear more than once in the selected column, whereas “One” means a key value appears only once in the selected column. In our Wide World Importers example earlier, the Sale table was on the many side, whereas the Date table was on the one side; a single date appeared only once in the Date table, though there could be multiple sales on the same date in the Sale table.

One to one is a special kind of relationship where a key value appears only once on both sides of the relationship. This type of relationship may be useful for splitting a single dimension with many columns into separate tables. You should only use this if you are confident that no duplicates will appear in this table, because duplicates will cause immediate errors in your data model.

Need More Review? One-To-One Relationships

One-to-one relationships are rarely encountered in real life. For advanced information on this type of relationships in Power BI, see “One-to-one relationship guidance” at https://docs.microsoft.com/en-us/power-bi/guidance/relationships-one-to-one.

Many-to-many relationships in this context refer to a direct relationship between two tables, neither of which is guaranteed to have unique keys. We review this type of relationship later in this chapter.