Building Data Models & Relationships
in Power BI
Introduction
Data modelling is a crucial step in Power BI that allows you to create relationships
between tables, define calculations, and optimize performance for data analysis. This
module covers essential concepts to help you build a strong foundation in Power BI
data modelling.
1️⃣ Understanding Tables & Data Model
What is a Data Model?
A data model in Power BI is a structured representation of data where tables are
connected through relationships to enable efficient analysis and reporting. It defines
how data is stored, related, and retrieved.
Types of Tables in Power BI:
● Fact Tables – Contain transactional data (e.g., sales, revenue, orders).
● Dimension Tables – Contain descriptive data (e.g., customers, products,
regions).
● Lookup Tables – Help categorize or filter data (e.g., date table for time-based
analysis).
Example:
Sales Table (Fact Table):
OrderID CustomerID ProductID Amount Date
1 101 5001 200 01-Jan-2024
2 102 5002 150 02-Jan-2024
Customers Table (Dimension Table):
CustomerID CustomerName
101 John Doe
102 Jane Smith
Best Practices:
✅ Use a star schema for better performance. ✅ Avoid using raw, unstructured data
directly in reports. ✅ Ensure data types are correctly assigned (e.g., Date, Integer,
Text).
2️⃣ Creating Relationships Between Tables
Why Relationships Matter
Relationships allow tables to interact and enable dynamic filtering. Without
relationships, you would need to merge data into a single table, which is inefficient.
Steps to Create Relationships:
1. Open Model View in Power BI.
2. Drag and drop a field from one table to another.
3. Define the Cardinality (One-to-Many, Many-to-Many, etc.).
4. Choose the Cross-filter direction (Single or Both).
Example Relationship:
● Sales Table has a CustomerID field that links to Customers Table.
● Relationship: One-to-Many (One customer can have multiple orders).
Best Practices:
✅ Always use unique keys for relationships. ✅ Use single-direction filtering when
possible for performance. ✅ Avoid unnecessary Many-to-Many relationships.
3️⃣ Understanding One-to-Many vs. Many-to-Many
Relationships
One-to-Many (1:M) Relationships
● Most commonly used relationship in Power BI.
● A single value in the primary table relates to multiple values in the secondary
table.
● Example: A single customer (one) can have multiple orders (many).
Example:
CustomerID CustomerName
101 John Doe
102 Jane Smith
OrderID CustomerID Amount
1 101 200
2 101 150
3 102 300
Many-to-Many (M:M) Relationships
● Occurs when two tables have non-unique values.
● Requires a bridging table to properly relate them.
● Example: Customers and Products (multiple customers can buy multiple
products).
Example:
CustomerID CustomerName
101 John Doe
102 Jane Smith
ProductID ProductName
5001 Laptop
5002 Phone
CustomerID ProductID
101 5001
102 5002
101 5002
Best Practices:
✅ Use a bridge table to manage M:M relationships. ✅ Ensure relationships are
properly defined to avoid circular dependencies.
4️⃣ Introduction to Star & Snowflake Schema
What is a Schema?
A schema is a way of structuring tables in a data model to optimize performance and
usability.
Star Schema ⭐ (Recommended)
● Fact table at the center.
● Connected to multiple dimension tables.
● Simple structure, best for Power BI performance.
● Example: Sales data linked to Customer, Product, Date tables.
Snowflake Schema ❄️
● Extension of Star Schema, where dimension tables are further normalized.
● More complex structure, better for large datasets.
● Example: A product table is linked to a category table instead of keeping the
category in the same table.
Best Practices:
✅ Use Star Schema whenever possible. ✅ Snowflake only when dealing with very
large datasets. ✅ Reduce unnecessary joins for better performance.
5️⃣ Creating Calculated Columns & Tables
What are Calculated Columns?
A calculated column is a new column added to a table using DAX (Data Analysis
Expressions).
Example:
Adding a Full Name column by combining First Name & Last Name.
Full Name = Customers[First Name] & " " & Customers[Last Name]
What are Calculated Tables?
● A new table created using DAX.
● Useful for aggregations, filtering, or joining tables.
Example:
Creating a summary table for total sales by region.
SalesByRegion = SUMMARIZE(Sales, Sales[Region], "Total Sales", SUM(Sales[Amount]))
Best Practices:
✅ Use DAX Measures instead of calculated columns when possible. ✅ Avoid creating
too many calculated columns for better performance. ✅ Keep calculated tables
minimal to optimize query performance.
Conclusion
Building a strong data model is the foundation of any Power BI report. By
understanding tables, relationships, schema design, and calculated fields, you can
optimize your Power BI performance and unlock powerful data insights.
What’s Next?
In the next module, we’ll dive into DAX (Data Analysis Expressions) and how to write
🚀
powerful formulas for analysis. Stay tuned!
