Power BI Cardinality and Filter Direction

Every relationship in Power BI carries two settings that control how tables talk to each other. Cardinality describes how many rows match on each side. Filter direction describes which way a selection travels. Correct settings produce correct totals, and wrong settings produce confusing numbers.

Cardinality Types

One-to-Many

One row in the first table matches many rows in the second. One customer places many orders. This type appears in most models and works best in a star schema.

Many-to-One

This is the same relationship viewed from the other side. Many orders belong to one customer. Power BI shows the symbols 1 and * on the relationship line to mark each side.

One-to-One

One row matches exactly one row on the other side. An employee table and an employee badge table fit this pattern. Merging the two tables into one often gives a simpler model.

Many-to-Many

Many rows match many rows on both sides. Students and clubs form such a link, since a student joins several clubs and a club holds several students. A bridge table in the middle keeps this pattern safe and clear.

One-to-Many            One-to-One            Many-to-Many (bridge)

Customer (1)           Employee (1)          Student      Club
    |                      |                    |           |
    | *                    | 1                  | 1         | 1
 Orders                  Badge               Enrollment (*)(*)

The Water Analogy for Filter Direction

A filter behaves like water flowing downhill. A selection on a dimension table, such as choosing the city Pune, flows down to the fact table and trims it to Pune rows. Water does not flow uphill by default, so a selection on the fact table never trims the dimension table.

  Customer  (pick "Pune")
      |
      |  filter flows down
      v
   Orders   (shows Pune rows only)

Single Versus Both Directions

Single direction is the default and the safest option. Filters flow from the one side to the many side only. Both directions let filters flow either way. Both directions help in rare cases, such as a slicer that must list only customers who placed orders.

Setting	Behavior	Risk
Single	Dimension filters fact	Very low
Both	Filters travel both ways	Slower models and unexpected totals

Changing the Settings

1. Open Model view from the left sidebar.
2. Double-click the relationship line between two tables.
3. Pick the Cardinality from the drop-down list.
4. Pick the Cross filter direction.
5. Select Save.

A solid line marks an active relationship. A dashed line marks an inactive relationship that a DAX formula can activate on demand.

Worked Example: Slicer Shows Too Many Names

A bookstore model links Authors to Books, and Books to Sales. A slicer on Author lists every author, including those with no sales this year. Changing the relationship between Books and Sales to both directions can trim the slicer. A safer fix places the slicer on a visual-level filter that hides authors with zero sales. Prefer filters over both-direction relationships whenever possible.

Warning Signs of Wrong Settings

• A total appears larger than the sum of its parts.
• Many-to-many appears between two tables that should connect one-to-many. Duplicate keys in a dimension cause this.
• A visual shows blank as a category. Fact rows with keys missing from the dimension cause this.

Key Points

• Cardinality describes how many rows match across a relationship.
• One-to-many fits most dimension-to-fact links.
• Filters flow from the one side to the many side by default.
• Use both-direction filtering sparingly and test the totals.
• Dashed lines mark inactive relationships.