Ravindra BagaleCourses & study guides Track your progress

Guides

Cardinality and Cross-Filter Direction in Power BI

By , Power BI & Data Analytics trainer LinkedIn Published: Last updated:

Cardinality is how many rows match on each end of a relationship: one, or many. Cross-filter direction is which way a slicer is allowed to walk. Single means the dimension filters the fact. Both means the filter can walk back, and that can hide cities you still wanted to see.

Friends! The relationship dialog has two settings students click and then forget. Why do they matter? They decide whether Oil filters only the sales number, or also empties the City slicer. How? We keep many-to-one and Single on a FreshBasket model, then we peek at Both with Oil, which exists only in Mumbai. Pune must not vanish by accident.

Quick answer

Vertical path:

  1. Double-click the relationship line in Model view.
  2. Cardinality: many-to-one (Sales many, Store one) for a normal star.
  3. Cross-filter direction: Single unless you have a written reason for Both.
  4. Product = Oil (only Mumbai) with Single: sales card 50, City slicer still lists Pune.
  5. The same slicer with Both can drop Pune, because the filter walks back to Store.
  6. Need Both for one measure only? Use CROSSFILTER inside CALCULATE. Do not change the whole model.
Single: dimension → fact
Both: dimension ⇄ fact (surprises live here)

Real example: Oil is only in Mumbai

Sales:

Order City Product Amount
A Mumbai Rice 100
B Mumbai Oil 50
C Pune Rice 40

Product is a dimension (Rice, Oil). Store is a dimension (Mumbai, Pune). Sales is the fact. Both relationships are many-to-one, from Sales to the dimension.

What Single does when the reader picks Oil:

  1. Product keeps the Oil row.
  2. The filter walks into Sales. Only order B matches.
  3. Number out: 50.
  4. The City slicer is not filtered by that walk. Pune is still on the list.
  5. If the reader then also picks Pune, the card can go blank. That is honest: Pune did not sell Oil.

What Both does on the Product relationship:

  1. Oil still keeps order B.
  2. The filter also walks back from Sales to Store.
  3. Store keeps only Mumbai, because order B is Mumbai.
  4. The City slicer can drop Pune even though you did not touch City.
  5. Readers think the shop closed in Pune. The setting closed it.

That is why the default is Single.

What do I need before this guide?

  • A real line already created (relationships).
  • Course: Relationships (cardinality and cross-filter direction live in that lesson).

Before and after (look at the tables first)

Before single direction Both direction hides Pune when Oil is selected.

Before - Both walks back. Both hid Pune

After single direction Single direction keeps Pune on the city list.

After - Single direction. Single. Card 50. List stays honest.

How to set the dialog

Cardinality and direction Cardinality and direction. Fact Sales * ——— 1 Store Cross-filter: Single Store filters Sales. Sales does not filter the city list. Cardinality and direction Fact Sales * ——— 1 Store Cross-filter: Single Store filters Sales. Sales does not filter the city list.

The relationship dialog sets cardinality (how many rows match) and cross-filter direction (which way a slicer walks).

  1. Model view. Double-click the Sales–Store line (or Manage relationships, then Edit).
  2. Cardinality: Many to one (*:1). The many side is the fact.
  3. If Power BI offers one-to-one, the “many” column is actually unique. Check the grain before you celebrate.
  4. If it offers many-to-many, stop. One side should be unique. Fix the key, then come back.
  5. Cross-filter direction: Single.
  6. Leave “Make this relationship active” on for the main path.
  7. Save. Repeat for Product and Date.

One-to-one is rare here. Employee and Employee Details are the textbook pair, and they are usually happier as one table. Do not use one-to-one to glue a fact to a dimension.

Single, Both, and one measure

Single vs Both Single vs Both. Oil is only in Mumbai. Sales = 50. Single: City slicer still lists Pune. Both: Pune can disappear. Keep Single unless you mean the surprise. Single vs Both Oil is only in Mumbai. Sales = 50. Single: City slicer still lists Pune. Both: Pune can disappear. Keep Single unless you mean the surprise.

Single lets Product filter Sales only. Both can also hide Pune on the City slicer when Oil exists only in Mumbai.

Sometimes a manager wants the City slicer to show only cities that sold the selected product. That is a real request. Two calm options:

  1. A visual-level filter on the City slicer: [Total Sales] is not blank. The model direction stays Single. Oil then shows Mumbai only, because Pune’s Oil sales are blank.
  2. A measure that turns Both on for that question only:
Sales Both Ways =
CALCULATE (
    [Total Sales],
    CROSSFILTER ( Sales[Product ID], Product[Product ID], Both )
)
  1. The model line stays Single for every other visual.
  2. This measure borrows Both for one calculation.
  3. You can explain it in a sentence. A silent Both on the relationship is harder to debug at 6 pm.

“Apply security filter in both directions” is a row-level security switch. Leave it off until the security guide says you need it. It is not a decoration.

Numbers to memorise

Choice Oil selected Sales card City slicer
Single Oil 50 Mumbai and Pune still listed
Both on Product Oil 50 Pune may disappear
No product filter — 190 Both cities

Rice is in both cities (100+40). An Oil test is the one that exposes Both. Test with a value that exists on only one side.

Mistakes and calm fixes

Symptom Likely cause Fix
City slicer loses Pune Cross-filter Both Set Single, or filter the slicer on non-blank sales
Cardinality stuck on many-to-many Duplicate keys Unique dimension key
Two actives refused Two paths between the same tables One active line; see USERELATIONSHIP
Totals change after “just a checkbox” Direction changed the filter path Put Single back and retest Oil

Ravindra Bagale's Tip

Interview line: “Cardinality is many-to-one from fact to dimension. Cross-filter stays Single. If I need Both, I use CROSSFILTER in one measure, not on every relationship.” Got it?

Practice task

  1. Build the three-row sales table with Rice and Oil.
  2. Set both relationships to many-to-one and Single.
  3. Select Oil. Confirm 50, and confirm Pune is still in the City slicer.
  4. Flip Product to Both. Watch the City slicer.
  5. Put Single back. Write one sentence about what changed.

Learn it properly

Course lesson:

Related: Relationships guide · Star schema

Got it? Cardinality says how many rows match. Cross-filter direction says which way the slicer walks. Keep Single. Next: star shape versus a snowflake with an extra hop. Let us go ahead.

Frequently asked questions

What is cardinality?

How many rows match on each end: one-to-many, one-to-one, or many-to-many.

What is cross-filter direction?

Which way a filter is allowed to walk along the relationship.

Why keep Single?

Both can hide dimension values, such as dropping Pune when you only picked a product.

When is Both useful?

When a slicer should list only items that have sales. Try a visual filter first, or CROSSFILTER on one measure.

What is one-to-one for?

Rare pairs such as Employee and Employee Details. Usually merge them into one table.

Course lesson?

Relationships, in the data modelling chapter, covers cardinality and direction.