Power BI Relationships & Cardinality: A Complete Beginner Guide
As coming from non-IT background, I must understand foundation for all the functions while learning Power BI
What queries and fields are, how important it is to organize the fields to the Query get the desired output in form of chart or KPI for the organization’s decision-making process.
Queries act as the backbone of your report, shaping the data before it reaches your visuals. A good data model reduces confusion, improves performance, and makes your report easier to maintain. When fields are organized properly, building dashboards becomes much easier.
Here I want to describe Cardinality in Power BI
Which is The Foundation of Effective Data Modeling
Let’s start with Star Schema
A star schema is a simple and efficient data modeling structure used in Power BI, where one central fact table is connected to multiple surrounding dimension tables. The model looks like a star — the fact table in the center and dimension tables around it.
It is the industry‑standard design because it makes reports faster, cleaner, and easier to understand.
A fact table stores the measurable business events,here in example we have a fact sales table, and dimension table are DimCustomer, DimProduct and DimDate.
These keys connect the fact table to each dimension table.
Examples:
CustomerKey → links to DimCustomer
ProductKey → links to DimProduct
DateKey → links to DimDate
Foreign keys allow filters to flow correctly. When a user selects a customer or product, Power BI uses these keys to find matching rows in the fact table.
For example, these are the actual numbers you want to analyze.
Sales Amount, Discount, profit, cost, Quantity
These values are aggregated in visuals (SUM, AVERAGE, MAX, etc)
A dimension table stores descriptive information about business entities. It sits around the fact table like the points of a star. Those are Customer Key, Product key, Date Key.
This key connects to the fact table’s foreign key.
Relationships between tables
In a star schema, relationships are the connections between the central fact table and the surrounding dimension tables. They define how tables interact, how filters flow, and how Power BI calculates results.
Without correct relationships, your star schema will not work — slicers won’t filter properly, totals will be wrong, and DAX will behave unpredictably.
How Relationships Work in a Star Schema
Dimension Table (1) → Fact Table () *
This means: Each dimension table has unique values (primary key).
The fact table has repeated values (foreign key).
The relationship connects the PK → FK.
This is called a one‑to‑many relationship, and it is the foundation of every correct Power BI model.
For example, we have 4 tables containing their respective primary key or Foreign Key
Table –
DimCustomer
CustomerKey -Customer name – State- Address
DimProduct
ProductKey- ProductName – Category
DimDate
DateKey – Year- Quarter
FactSales
CustomerKey- ProdutKey- DateKey -TotalAMount- Quantity.
Relationships:
DimCustomer.CustomerKey (1) → FactSales.CustomerKey ()*
DimProduct.ProductKey (1) → FactSales.ProductKey()*
DimDate.DateKey (1) → FactSales.DateKey()*
Everything works smoothly because relationships are clean and directional.
Relationships are crucial in Star Schema model
- Because of correct filter flow – filter must flow dimension to fact. This ensures slicers, visuals, and DAX return accurate results.
- SUM, COUNT, AVERAGE, and time intelligence functions rely on proper relationships.
- DAX formulas become simpler because the model is predictable.
- Relationships prevent: Duplicate values, Wrong totals, Circular filtering, Slow performance.
There are three kinds of relationships or cardinality between the tables, which are as follows:
One-to-Many
Dimension (1) → Fact (*)
This is the correct and recommended pattern.
Imagine the list of students is in DimStudent table and FactResult table contain list of subject.
So, Student is unique, but he can appear for more than one subject from result table.
Or likewise one customer has many sales orders.
One-to- One
Each row in Table A matches exactly one row in Table B.
And each row in Table B matches exactly one row in Table A.
Imagine at the student table every student can select only one subject as an elective subject.
Many-to-many
Multiple rows in Table A can match multiple rows in Table B.
And multiple rows in Table B can match multiple rows in Table A.
It’s Complex and can cause unclear filter behavior; often handled using a bridge table. Example: Students enrolled in multiple classes where each class has multiple students.Cross Filter Direction
Cross filter direction controls how filters flow between two related tables in your Power BI data model. When you create a relationship (usually between a dimension table and a fact table), Power BI needs to know which table can filter the other.
In a star schema, the dimension table usually filters the fact table (single direction).
When you need filters to flow both ways — especially in many‑to‑many relationships — you can enable bi‑directional filtering. Use bi‑directional filtering carefully because it can introduce ambiguity and impact performance.
Types of Cross Filter Direction
Single Direction
Filters flow from the dimension table → fact table meaning DimDate filters FactSales which Keeps the model clean, predictable, and fast.
Both Directions (bi‑directional)
Filters flow both ways meaning DimCustomer ↔ FactSales . Useful when you need filtering to work across multiple dimensions, helps when building complex reports where slicers must affect multiple related tables.
It’s used when you have many-to-many relationships, when you need a slicer to filter both tables.
To set cross filter direction you must go model view – click on relationship line -in the properties pane – Cross filter direction selects single or both.
Relationship's cardinality
In Power BI Desktop model view, you can interpret a relationship's cardinality type by looking at the indicators (1 or *) on either side of the relationship line. To determine which columns are related, select, or hover the cursor over, the relationship line to highlight the columns.
Power BI Automatically Creates Relationships — But You Can Control It
When you import multiple tables into Power BI, the engine tries to detect relationships automatically. It looks at column names, data types, and patterns to guess how tables should connect. For beginners, this feels convenient — but for professional data modeling, automatic relationships can sometimes create wrong or unnecessary connections.
That’s why Power BI gives you full control to disable automatic relationship creation and build your own clean, star‑schema‑based relationships.
Here are the steps to disable Auto‑Relationships
Go to File- Select Options and Settings- Click Options- Under Current File, choose Data Load- Find the section Relationships- Turn off: Autodetect new relationships after data is loaded and Autodetect relationships.
Once you disable these options, Power BI will stop creating relationships automatically, giving you full control.
Build Relationships Manually
After disabling auto‑relationships, you can create your own:
Go to Model View- Drag the key from the dimension table- Drop it onto the matching key in the fact table
Set Cardinality (usually One‑to‑Many)
Cross‑filter direction (Single direction recommended)
Active or Inactive relationship.
