๐ Power BI Level 3 โ Data Modeling & Relationships
Once your data is clean, the next step is to build a proper data model.
This is where you decide how your tables connect and how Power BI should understand your data.
๐น 1. What Is a Data Model?
A data model is the structure that connects your tables.
For example, you might have:
Sales
Order_ID
Customer_ID
Product_ID
Date
Sales
Quantity
Customers
Customer_ID
Customer_Name
Region
Products
Product_ID
Product_Name
Category
Date
Date
Month
Quarter
Year
These tables are connected through relationships.
๐น 2. Fact Table
A fact table contains business transactions and numerical values.
Example:
Sales
It may contain:
โข Sales Amount
โข Quantity
โข Cost
โข Profit
โข Order ID
Think:
Fact = What happened?
๐น 3. Dimension Table
Dimension tables describe the facts.
Examples:
Customer โ Who?
Product โ What?
Date โ When?
Region โ Where?
For example:
Customer
Customer_ID
Customer_Name
Region
๐น 4. Star Schema
A common Power BI model looks like this:
Customers
Products โโโโ Sales โโโโ Date
Region
The fact table is in the middle and dimension tables surround it.
This is called a Star Schema.
๐น 5. Primary Key
A primary key uniquely identifies a record.
For example:
Customer_ID
101
102
103
Each ID identifies one customer.
๐น 6. Foreign Key
The Sales table can contain the same customer multiple times:
Customer_ID
101
101
102
101
103
Here, "Customer_ID" is used to connect Sales with Customers.
So:
Customers โ Primary Key
Sales โ Foreign Key
๐น 7. One-to-Many Relationship
The most common relationship in Power BI is:
One Customer โ Many Sales
Customers Sales
1 *
| |
Customer_ID โโโโโโโโโ Customer_ID
This is called a:
1 : * relationship
๐น 8. Why Relationships Matter
Suppose you select:
Region = West
Power BI needs to know which sales belong to customers from the West region.
The relationship allows the filter to travel from:
Customers
โ
Sales
Without a proper relationship, your visuals may show incorrect results.
๐น 9. Cardinality
Cardinality describes how records relate between two tables.
Common types:
1 : * โ One-to-Many
1 : 1 โ One-to-One
โข : * โ Many-to-Many
For most Power BI analytical models, 1-to-many relationships are the most common.
๐น 10. Many-to-Many Relationships
Many-to-many relationships can make models more complicated.
For example:
Customers โ Products
A customer can buy many products.
A product can be purchased by many customers.
Instead of directly connecting them in some cases, a bridge table can be used.
Customers
โ
Bridge Table
โ
Products
๐น 11. Date Table
A proper Date table is extremely important for Power BI.
It can contain:
Date
Day
Month
Month Number
Quarter
Year
Year-Month
For example:
Date | Month | Quarter | Year
01-Jan-26 | January | Q1 | 2026
02-Jan-26 | January | Q1 | 2026