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

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education