Data modelling is the foundation of effective data analysis in Power BI. A well-structured model ensures faster performance, accurate calculations, and easier report building. This article breaks down everything you need to know—from SQL joins and Power BI relationships to schemas and practical implementation steps.
What is Data Modelling?
Data modelling is the process of organising data into tables and defining how those tables relate to each other so that analysis becomes meaningful and efficient.
In Power BI, data modeling involves:
- Structuring tables (Fact and Dimension)
- Defining relationships between tables
- Optimizing performance and usability
SQL Joins Explained (With Examples)
Joins combine data from two or more tables based on a common column.
Examples:
Customers
| CustomerID | Name |
|---|---|
| 1 | John |
| 2 | Mary |
| 3 | Alex |
Orders
| OrderID | CustomerID |
|---|---|
| 101 | 1 |
| 102 | 2 |
| 103 | 4 |
INNER JOIN
Returns only matching records from both tables.
Result:
| CustomerID | Name | OrderID |
|---|---|---|
| 1 | John | 101 |
| 2 | Mary | 102 |
Use case: When you only want valid matches (e.g., customers who made purchases).
LEFT JOIN
Returns all records from the left table + matching from the right.
Result:
| CustomerID | Name | OrderID |
|---|---|---|
| 1 | John | 101 |
| 2 | Mary | 102 |
| 3 | Alex | NULL |
Use case: Show all customers, even those without orders.
RIGHT JOIN
Returns all records from the right table + matching from the left.
Result:
| CustomerID | Name | OrderID |
|---|---|---|
| 1 | John | 101 |
| 2 | Mary | 102 |
| NULL | NULL | 103 |
Use case: Show all orders, even if customer info is missing.
FULL OUTER JOIN
Returns all records from both tables.
Result:
| CustomerID | Name | OrderID |
|---|---|---|
| 1 | John | 101 |
| 2 | Mary | 102 |
| 3 | Alex | NULL |
| NULL | NULL | 103 |
Use case: Complete data reconciliation.
LEFT ANTI JOIN
Returns rows from left table that have no match in right table.
Result:
| CustomerID | Name |
|---|---|
| 3 | Alex |
Use case: Identify customers who never purchased.
RIGHT ANTI JOIN
Returns rows from right table with no match in left table.
Result:
| OrderID | CustomerID |
|---|---|
| 103 | 4 |
Use case: Identify orphan records (e.g., invalid orders).
Joins in Power BI (Power Query)
Power BI implements joins in Power Query.
Steps:
- Go to Home → Transform Data
- Select a table
- Click Merge Queries
- Select second table
- Choose matching column(s)
- Select join type:
- Inner
- Left Outer
- Right Outer
- Full Outer
- Left Anti
- Right Anti
- Expand columns to finalize
Relationships in Power BI
Unlike SQL joins (which combine tables physically), Power BI relationships connect tables logically.
Types of Relationships
1. One-to-Many (1:M)
- One record in Table A → Many in Table B
- Example: Customers → Orders
Most common relationship
2. Many-to-Many (M:M)
- Many records in both tables
- Example: Students ↔ Courses
3. One-to-One (1:1)
- One record matches exactly one record
- Example: Employee ↔ Employee Details
Cardinality
Defines how tables relate:
- One-to-Many
- Many-to-One
- Many-to-Many
Cross-Filter Direction
Controls how filters flow between tables.
- Single direction → One way (recommended)
- Both directions → Two-way filtering (use carefully)
Active vs Inactive Relationships
- Active relationship → Default used in visuals
-
Inactive relationship → Exists but is not used unless activated via DAX (
USERELATIONSHIP)
Example:
- Order Date (Active)
- Ship Date (Inactive)
Creating Relationships in Power BI
Method 1: Model View
- Go to Model View
- Drag one column to another
- Relationship is created automatically
Method 2: Manage Relationships
- Go to Home → Manage Relationships
- Click New
- Select:
- Tables
- Columns
- Cardinality
- Cross-filter direction
- Click OK
Joins vs Relationships (Key Difference)
| Feature | Joins | Relationships |
|---|---|---|
| Where used | Power Query | Data Model |
| Result | Combines tables | Keeps tables separate |
| Performance | Can increase size | More efficient |
| Flexibility | Static | Dynamic |
Fact vs Dimension Tables
Fact Table
Contains measurable data (numbers)
Examples:
- Sales
- Revenue
- Quantity
Dimension Table
Contains descriptive attributes
Examples:
- Customer Name
- Product Category
- Date
Example Model
FactSales
- OrderID
- CustomerID
- ProductID
- SalesAmount
DimCustomer
- CustomerID
- Name
DimProduct
- ProductID
- Category
Data Modeling Schemas
Star Schema
- One central fact table
- Connected to multiple dimension tables
Snowflake Schema
- Dimensions are normalized (split into multiple tables)
Example:
- Product → Category → Department
Flat Table (Denormalized / DLAT)
- All data in one table
Use Cases
| Schema | When to Use |
|---|---|
| Star | Most Power BI reports |
| Snowflake | Complex hierarchical data |
| Flat Table | Small datasets or quick analysis |
Role-Playing Dimensions
A role-playing dimension is a table used multiple times for different purposes.
Example: Date Table
- Order Date
- Ship Date
- Delivery Date
In Power BI:
- Duplicate the Date table
- Create separate relationships
Common Data Modeling Issues
Ambiguous relationships
Happens with multiple paths between tables
Many-to-many confusion
Can lead to incorrect aggregations
Circular relationships
Causes errors
Poor performance
Caused by flat tables or too many joins
Solution:
- Use star schema
- Avoid unnecessary bi-directional filters
- Keep relationships simple
Step-by-Step: Building a Model in Power BI
Step 1: Load Data
- Home → Get Data
Step 2: Clean Data (Power Query)
- Remove duplicates
- Handle nulls
- Merge tables if needed
Step 3: Create Relationships
- Go to Model View
- Drag and connect tables
Step 4: Validate Model
- Check cardinality
- Ensure no ambiguous paths
Step 5: Optimize
- Use star schema
- Reduce columns
- Avoid many-to-many unless necessary
Conclusion
Data modeling in Power BI is not just technical, it’s strategic. Understanding joins helps you prepare data, while relationships allow you to analyze it efficiently. By structuring your data into fact and dimension tables and choosing the right schema (preferably star), you create a model that is both powerful and scalable.
Mastering these concepts transforms Power BI from a simple visualization tool into a robust analytics engine and ultimately changes how you interpret and interact with data.
Top comments (0)