Work With Relationship & Cardinality

Work With Relationship And Cardinality

Unlike other database management systems, Power BI has the concept ofdirectionality to a relationship. This directionality, or cardinality, plays an important role in filtering data between multiple tables. When you load data, Power BI automatically looks for relationships that exist within the data by matching column names. You can also use Manage Relationships to edit these options manually.

For example, you’ve retrieved many tables from the Sales database, and the following image is an example of your data model. Power BI has autodetected several relationships, but you can’t discern what they mean.

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 unique 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.