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.