Skip main navigation

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.
This article is from the free online

Microsoft Power BI: Advanced Data Analysis and Visualisation

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now