This lesson's interactive features are locked, please to keep using them
Cardinality and Cross Filters
Cardinality (one-to-one, one-to-many, many-to-many) describes the type of relationship between two tables.
When looking at the Model View we can actually see the cardinality represented visually:
This diagram shows a standard one-to-many relationship between products (1) and sales (*).
When you right-click on a relationship and click "properties" you'll see a more detailed view:
For most data models, you'll use many-to-one or one-to-many relationships. Many-to-many and especially one-to-one are more rare.
Note: Cross Filter Direction
In the properties pane, there's a setting that controls how filters move between the two tables.
Single (recommended) means the filter flows from the one side into the many side
Both allows filters to move in both directions
Imagine a Schools table (one) related to a Students table (many), and that "Lakeside High" happens to not have any students before the year 2012. Here's how single and both direction filtering would work:
| Filter | Single Direction | Both Directions |
|---|---|---|
| Schools to "Lakeside High" | Students are filtered to Lakeside High students | Students are filtered to Lakeside High students |
| Students to "Before 2012" | Schools table is unaffected (good) | "Lakeside High" is filtered from schools table (bad) |
It feels strange that filtering students affects the schools table, and that rule of thumb applies to most scenarios.
In my 5+ years of using Power BI I've only used "Both" a handful of times.