Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Cardinality and Cross-Filter Direction


Share

What Is Cardinality?

Every relationship has two settings that you saw in the Edit relationship dialog: Cardinality and Cross-filter direction. This chapter explains both.

Cardinality tells how many rows on each side of a relationship can have the same value.

Look at ProductID.

In Products, each ID appears exactly once.

In Sales, each ID appears on many rows:

ProductIDProductRows in ProductsRows in Sales
P01Poke Ball1512
P02Great Ball1344
P03Ultra Ball1179
P04Potion1435
P05Super Potion1367
P06Hyper Potion1155
P07Revive1153
P08Antidote1251
P09Paralyze Heal1185
P10Awakening1159
Total102,740

One product, many sales.

Seen from Sales, this is a many-to-one relationship, written *:1.

Stores and Sales work the same way: each StoreID is on one row of Stores, and on 311 to 650 rows of Sales.


The Cardinality Options

In the Edit relationship dialog, the Cardinality list has four options:

OptionMeaningExample
Many to one (*:1)Values repeat in the first table and are unique in the secondSales to Products, Sales to Stores
One to many (1:*)The same, with the tables the other way aroundProducts to Sales
One to one (1:1)Values are unique in both tablesStores to a table of store details, with one row per store
Many to many (*:*)Values repeat in both tablesStores to a table of targets per region and month, related on Region

Many to one and one to many are the same relationship, seen from different ends.

Which one the dialog shows depends only on which table is listed first.

Power BI chooses the cardinality for you by looking at the data.

In a star schema, almost every relationship is many-to-one: from the fact table to a dimension table.

One-to-One

A one-to-one relationship usually means that two tables describe the same thing.

Say you had a StoreDetails table with the name of the manager of each of the six stores.

It is simpler to add that column to Stores with Merge Queries, and keep one table per thing.

Many-to-Many

Imagine sales targets set per region and month.

In that targets table, each region appears on 12 rows, one per month.

In Stores, each region appears on 2 rows, one per store.

Neither side is unique, so a relationship on Region would be many-to-many.

Many-to-many relationships are an advanced topic.

Totals can surprise readers who do not know how the tables are connected.

The Poke Mart model does not need them.


Cross-Filter Direction

A relationship does more than connect two tables.

It also carries filters from one table to the other.

When you select Status Healers in a slicer on Category, you filter the Products table.

The relationship carries that filter on to Sales, so only the orders for Status Healers are added up.

The cross-filter direction says which way filters can travel:

Single

Products1 side
1*
Salesmany side

Both

Products1 side
1*
Salesmany side
  • Single: filters travel one way, from the one side to the many side: from Products to Sales. Model view shows one arrow in the middle of the line, pointing to Sales.
  • Both: filters travel in both directions. Model view shows a double arrow.

All relationships in the Poke Mart model use Single.

You change the direction in the Edit relationship dialog:

The Edit relationship dialog for Sales and Products with the Cross-filter direction list open, showing the options Single and Both



Example: From Products to Sales

Continue in poke_mart_model.pbix, the file you built in the Star Schema and Relationships chapters.

Go to Report view and build a new table visual:

  1. Click an empty spot on the canvas and select Table in the Visualizations pane.
  2. In the Data pane, select Region from Stores, then Quantity from Sales.
  3. Click an empty spot on the canvas, select Slicer, and select Category from Products.
  4. In the slicer, select Status Healers.
RegionSum of Quantity
(no selection)
Sum of Quantity
(Status Healers)
Hoenn7,7421,158
Johto9,1111,220
Kanto7,6351,121
Total24,4883,499

Two filters meet in Sales here:

  • The slicer filters Products to 3 of its 10 rows: Antidote, Paralyze Heal and Awakening. That filter travels to Sales and keeps their 595 orders.
  • Each row of the table filters Stores to one region. That filter also travels to Sales.

Both filters flow from a dimension table to the fact table, in the direction of the arrows.

This is exactly what Single direction is for.

Select Status Healers in the slicer again to clear the selection.


Example: Not From Sales Back to Products

Now try the other way: filter Sales, and count products.

  1. Click an empty spot on the canvas and select Table.
  2. Select OrderID from Sales. It lists the order numbers, because you set it to Don't summarize in the Star Schema chapter.
  3. Select Product from Products.
  4. In the Columns well of the Visualizations pane, select the arrow next to Product, and select Count.
OrderIDCount of Product
100110
100210
100310
100410
......
Total10

Order 1001 is one row in Sales: one Hyper Potion.

So why does the table say 10?

Each row of the table filters Sales to one order.

With Single direction, that filter cannot travel from Sales back to Products.

Products is not filtered at all, so Power BI counts all 10 products for every order.

Change the direction to see the difference:

  1. In Model view, double-click the line between Sales and Products.
  2. Set Cross-filter direction to Both, and select Save.

The line now shows a double arrow:

Model view with the relationship line between Sales and Products showing a double arrow for cross-filter direction Both, next to the single arrow on the line between Sales and Stores

Back in Report view, the counts change:

OrderIDProduct in the orderCount of Product
(Single)
Count of Product
(Both)
1001Hyper Potion101
1002Poke Ball101
1003Great Ball101
1004Antidote101
............
Total1010

With Both, the filter on Sales travels up to Products, and each order counts its one product.

The total is still 10, because all orders together contain all 10 products.

Now set the direction back to Single before you continue.


Why Single Is the Recommended Default

If Both gives the right answer here, why not use it everywhere?

Microsoft recommends using Both as little as possible, for three reasons:

  • Speed: filters that travel both ways take more work, so reports can get slower, especially when a model has many such relationships.
  • Surprises: a selection in one visual can change what other visuals and slicers list. For example, a Product slicer starts to hide products that have no orders for the current selection. Some readers find this confusing.
  • Ambiguity: when there is more than one path between two tables, a filter could travel either way, and the result would depend on the path. Power BI does not allow a Both setting that would make the model ambiguous.

Most questions can be answered with Single, if you count in the right table.

The fact table already knows which product each order holds:

  1. In the table visual, remove Count of Product.
  2. Select ProductID from Sales, open its menu in the Columns well, and select Count (Distinct).

The new column is called Count of ProductID, even though it counts distinct values.

Each order now shows 1, and the total shows 10, with Single direction.

If you ever need Both for a single calculation, DAX has a CROSSFILTER function that turns it on inside one formula, and leaves the relationship itself on Single.

Keep filters flowing from the dimension tables to the fact table.

That is how a star schema is meant to work.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.