11. Data Modelling

11.4 Relationships in Power BI with Example: Cardinality and Direction

By Ravindra Bagale · Last updated: 6 October 2026

In short: A relationship tells Power BI which key column links two tables.

Quick answer: the short, self-contained version of this lesson is the guide Power BI Cardinality and Filter Direction.

Start with a problem

Ruhi adds a City slicer from DarkStore, but every city shows ₹2,100.

Explain simply

A relationship tells Power BI which key column links two tables. Filters flow from the one side (DarkStore) to the many side (Orders).

Show the data

Matrix with NO relationship:

About this data: This table uses columns: City, Amount (₹). This table uses columns: City, Total Sales (₹). This table uses columns: City (DarkStore), Total Sales. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv, dark_stores_maharashtra.csv (source: src/practice/).

Demonstrate

Show the result

Your result should look like this (fictional sample):

Explain what happened

Each order's Store now matches exactly one DarkStore row. A City filter selects stores, and the stores filter Orders along the arrow.

Explore further

• Inactive (dotted) relationships are used with USERELATIONSHIP (Module 13.12).
• Use Both direction only for a reason, such as the bridge table in 11.11.

Detailed reference for this lesson:

First look at the data (before → after):

Model view: drag DarkStore[Store ID] → Orders[Store ID]
Cardinality One to many (1:*) · Cross filter: Single

What you should see on screen after the steps (sample layout — fictional data, not a Microsoft screenshot).

Power BI often creates relationships automatically when column names and types match (autodetect). Always check them in Model view or Modeling › Manage relationships.

To create a relationship manually: in Model view, drag the key column from one table onto the matching column of another table, or use Manage relationships › New.

Cardinality

In the diagram, 1 and * labels appear at each end of the relationship line.

Cross-filter direction

• Single (default for 1:): filters flow from the one side (dimension) to the many* side (fact). Selecting "Snacks" in Product[Category] filters Orders.
• Both (bi-directional): filters flow in both directions. Sometimes needed (e.g. to filter a slicer of customers to only those who bought), but it can cause ambiguity (संदिग्धता – एकापेक्षा जास्त अर्थ किंवा मार्ग), unexpected results and slower performance.

Active and inactive relationships

Mhanje asa: between two tables, only one relationship can be active at a time. In our sample, Orders has two dates – Order Date and Delivered Date – both related to Date[Date]. They usually match, pan an order placed at 11:55 pm may be delivered after midnight, on the next date:

• Date[Date] → Orders[Order Date] is active (solid line). All visuals use it by default.
• Date[Date] → Orders[Delivered Date] is inactive (dotted line). It is used only when a measure activates it with USERELATIONSHIP:

Sales by Delivered Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(Orders[Delivered Date], 'Date'[Date])
)

Alternative approach: create a second date table (a role-playing dimension, e.g. Delivered Date) – but this increases model size.

Relationship properties dialog

Double-click a relationship line to open it. You can set these options:

1. Cardinality: how many rows on each side match (one-to-many, one-to-one, many-to-many).
2. Cross filter direction: which way filters flow.
3. Make this relationship active.
4. Apply security filter in both directions: for RLS with two-way filters.
5. Assume referential integrity: DirectQuery only. It allows inner joins, so queries run faster.

Fix common mistakes

Practise independently

Think like a professional

Every relationship needs a reason: key, cardinality, direction. Check for (Blank) rows after each refresh; they reveal missing dimension members.

Ravi Sir's Cards for this lesson

Revision cardInterview card

मराठी cards