Relationships & Cardinality

Power BI · Data Modeling

Theory

A relationship links a column in one table to a column in another so filters can flow between them. Cardinality describes how many rows match on each side. One-to-many (1:*) is the normal case: each CustomerID appears once in Customers but many times in Sales. One-to-one (1:1) means both sides are unique, which often signals the tables could be merged. Many-to-many (*:*) means neither side is unique, for example relating Sales[Region] to a Targets table that also repeats regions; it works but needs care because totals can surprise you.

Only one active relationship can exist between two tables; others are inactive (dashed lines) and can be used in a measure with USERELATIONSHIP. With Customers 1:* Sales in place, putting Customers[City] on a table visual with [Total Sales] gives Jaipur ₹2,700 (Asha's two orders), Pune ₹2,400, Chennai ₹600 and Delhi ₹500.

Why we use it

Relationships let a filter on one table affect another, so you never need lookup formulas to bring City into Sales. Correct cardinality keeps totals accurate and the engine efficient.

When to use it

Use one-to-many from every dimension to its fact table as the default. Investigate any one-to-one or many-to-many suggestion: often a key has duplicates or blanks that should be fixed, or a bridge or dimension table is missing.

How it works

  1. Need to link two tables
  2. Drag the key column onto the matching column
  3. Is the key unique on one side? Yes: Power BI sets one-to-many. No: Fix duplicates or plan a many-to-many design.
  4. Set cross-filter direction (Single by default)
  5. Test with a visual: dimension column + measure
  6. Filters now flow between the tables

Syntax

Model view → drag column onto column, or Home → Manage relationships → New
Cardinality: Many to one (*:1) | One to one (1:1) | One to many (1:*) | Many to many (*:*)
Cross filter direction: Single | Both
Inactive relationships: use USERELATIONSHIP(col1, col2) inside CALCULATE

Example

1. Manage relationships → New: Sales[CustomerID] → Customers[CustomerID].
2. Power BI detects Many to one (*:1) because CustomerID is unique in Customers.
3. Cross filter direction: Single (Customers filters Sales).
4. Table visual: Customers[City], [Total Sales]
   Jaipur ₹2,700 | Pune ₹2,400 | Chennai ₹600 | Delhi ₹500
5. If detection says many-to-many, check Customers for a duplicate CustomerID.

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic