DEV Community

Samuel Mwai
Samuel Mwai

Posted on

Data Modelling in Power BI: Joins, Relationships and Schemas Explained

#analytics #database #data

Data modelling is one of the most important concepts in Power BI. A good data model allows reports to produce accurate results, perform efficiently, and remain easy to maintain. A poor data model, on the other hand, can lead to incorrect calculations, confusing relationships, duplicate data, and slow reports.

In Power BI, data modelling involves organising tables and defining how those tables interact with one another. Three concepts are particularly important: joins, relationships, and schemas.

1. What Is Data Modelling?

Data modelling is the process of organising data into tables and defining the connections between those tables.

For example, imagine a company has three tables:

  • Customers – contains customer information.
  • Products – contains product information.
  • Sales – contains information about each sale.

The Sales table might contain:

SaleID CustomerID ProductID Quantity SalesAmount
1001 C001 P01 2 2,000
1002 C002 P03 1 1,500
1003 C001 P02 4 3,200

The Customers table could contain:

CustomerID CustomerName Country
C001 John Kenya
C002 Mary Uganda

The Products table could contain:

ProductID ProductName Category
P01 Laptop Electronics
P02 Monitor Electronics
P03 Printer Electronics

Power BI can connect these tables using CustomerID and ProductID.

This creates a data model that allows us to answer questions such as:

  • How much did each customer spend?
  • Which products generated the most revenue?
  • Which country generated the highest sales?
  • How many products were sold in each category?

2. Joins Explained

A join combines data from two or more tables based on a common column.

Joins are commonly encountered when using Power Query in Power BI.

For example, suppose we have:

Customers

CustomerID CustomerName
C001 John
C002 Mary
C003 Peter

Sales

CustomerID SalesAmount
C001 2000
C002 1500
C004 3000

The common column is CustomerID.

A join can combine these tables using that column.

Types of Joins

Power Query provides several join types.

Inner Join

An Inner Join returns only records that exist in both tables.

In our example, C001 and C002 exist in both tables, while C003 and C004 do not.

The result would be:

CustomerID CustomerName SalesAmount
C001 John 2000
C002 Mary 1500

Inner joins are useful when you only want records with matching values in both tables.

Left Outer Join

A Left Outer Join keeps every record from the left table and adds matching records from the right table.

For example:

CustomerID CustomerName SalesAmount
C001 John 2000
C002 Mary 1500
C003 Peter null

Peter remains in the result because he exists in the left table, even though he has no matching sales record.

This is one of the most commonly used joins.

Right Outer Join

A Right Outer Join keeps every record from the right table and adds matching records from the left table.

Using our example, C004 would be included because it exists in the right table.

Full Outer Join

A Full Outer Join keeps all records from both tables.

It includes matching and non-matching records.

CustomerID CustomerName SalesAmount
C001 John 2000
C002 Mary 1500
C003 Peter null
C004 null 3000

Anti Joins

Anti joins are useful for finding records that do not have a match.

For example, a Left Anti Join could identify customers who have never made a purchase.

This can be extremely useful for data-quality checks and business analysis.

3. Joins vs Relationships

Joins and relationships are related concepts, but they are not the same thing.

A join physically combines columns from tables into a new table during data preparation.

A relationship connects existing tables within the Power BI data model.

For example:

Customers
CustomerID
CustomerName
Country
      |
      | CustomerID
      |
      ↓
Sales
SaleID
CustomerID
ProductID
SalesAmount
Enter fullscreen mode Exit fullscreen mode

The tables remain separate, but Power BI knows how they are connected.

This distinction is important.

Joins are mainly used for:

  • Combining columns
  • Cleaning data
  • Preparing data
  • Creating a new query/table

Relationships are mainly used for:

  • Connecting tables
  • Filtering data
  • Creating measures
  • Building reports
  • Supporting DAX calculations

In many Power BI models, it is better to keep related tables separate rather than repeatedly joining everything into one large table.

4. Relationships in Power BI

A relationship tells Power BI how two tables are connected.

For example:

Customers[CustomerID]
          |
          |
          ↓
Sales[CustomerID]
Enter fullscreen mode Exit fullscreen mode

Power BI can then understand that a particular sale belongs to a particular customer.

Relationships are created using columns that contain related values.

Usually, one table contains a unique value while another table contains repeated values.

For example:

Customers

CustomerID CustomerName
C001 John
C002 Mary
C003 Peter

Each CustomerID appears once.

Sales

SaleID CustomerID Amount
1 C001 1000
2 C001 500
3 C002 800

CustomerID can appear many times in Sales.

This creates a one-to-many relationship.

5. Cardinality

Cardinality describes how many records in one table can be related to records in another table.

Power BI supports several types.

One-to-Many (1:*)

This is the most common relationship in a well-designed Power BI model.

For example:

Customers  1 ───────── *  Sales
Enter fullscreen mode Exit fullscreen mode

One customer can have many sales.

The Customers table is the one side, while Sales is the many side.

Many-to-One (*:1)

This is essentially the same relationship viewed from the opposite direction.

Sales  * ───────── 1  Customers
Enter fullscreen mode Exit fullscreen mode

Many sales belong to one customer.

One-to-One (1:1)

Each record in one table corresponds to exactly one record in another table.

Employee  1 ───────── 1  EmployeeDetails
Enter fullscreen mode Exit fullscreen mode

This relationship is less common and should be used carefully.

Many-to-Many (:)

Multiple records in one table can correspond to multiple records in another.

For example:

Students  * ─────── *  Courses
Enter fullscreen mode Exit fullscreen mode

A student can take many courses, while a course can have many students.

Many-to-many relationships can be useful, but they can also create ambiguity and unexpected filtering behaviour. A bridge table is often a better solution.

6. Primary Keys and Foreign Keys

Relationships usually depend on keys.

A primary key uniquely identifies each record in a table.

For example:

CustomerID
C001
C002
C003
Enter fullscreen mode Exit fullscreen mode

Each CustomerID is unique.

A foreign key is a column that refers to a key in another table.

For example, the Sales table may contain:

CustomerID
C001
C001
C002
C003
Enter fullscreen mode Exit fullscreen mode

Here, CustomerID is a foreign key because it refers to CustomerID in the Customers table.

A typical model therefore looks like:

Customers
----------
CustomerID ← Primary Key
CustomerName
Country

       1
       |
       |
       *
Sales
----------
SaleID ← Primary Key
CustomerID ← Foreign Key
SalesAmount
Enter fullscreen mode Exit fullscreen mode

7. Schemas Explained

A schema is the overall structure or organisation of tables and their relationships.

When working with Power BI, two important schema designs are the Star Schema and Snowflake Schema.

8. Star Schema

The Star Schema is generally the preferred modelling approach for Power BI.

It gets its name because the model looks like a star.

At the centre is a fact table, surrounded by dimension tables.

For example:

                 Customers
                     |
                     |
Products ─────── Sales ─────── Date
                     |
                     |
                 Employees
Enter fullscreen mode Exit fullscreen mode

The central Sales table is the fact table.

The surrounding tables are dimension tables.

Fact Tables

A fact table contains business events or transactions.

For example:

Sales
----------------
SaleID
DateID
CustomerID
ProductID
Quantity
SalesAmount
Enter fullscreen mode Exit fullscreen mode

A fact table normally contains numerical values that can be aggregated, such as:

  • Sales
  • Revenue
  • Quantity
  • Cost
  • Profit
  • Discount

These values are often called measures or metrics.

Dimension Tables

Dimension tables provide descriptive information about the facts.

For example:

Customer Dimension 

CustomerID
CustomerName
Gender
Country
Age
Enter fullscreen mode Exit fullscreen mode

Product Dimension 

ProductID
ProductName
Category
Brand
Enter fullscreen mode Exit fullscreen mode

Date Dimension 

DateID
Date
Year
Month
Quarter
Day
Enter fullscreen mode Exit fullscreen mode

Dimensions allow users to analyse facts from different perspectives.

For example:

Total sales by country

uses the Customer dimension.

Total sales by product category

uses the Product dimension.

Total sales by month

uses the Date dimension.

9. Snowflake Schema

A Snowflake Schema is similar to a Star Schema, but dimension tables are further divided into additional tables.

For example:

                 Country
                    |
                    |
Customers ─────── Sales ─────── Products
                                |
                                |
                             Category
Enter fullscreen mode Exit fullscreen mode

Instead of keeping all product information in one Products table, some information may be separated into another table.

For example:

Products
---------
ProductID
ProductName
CategoryID
Enter fullscreen mode Exit fullscreen mode

and:

Categories
----------
CategoryID
CategoryName
Enter fullscreen mode Exit fullscreen mode

This reduces repeated information but creates additional relationships.

10. Star Schema vs Snowflake Schema

Feature Star Schema Snowflake Schema
Structure Simple More complex
Dimensions Usually denormalised More normalised
Number of tables Fewer More
Relationships Easier More complex
Power BI usability Excellent Good
Report development Easier More difficult
Performance Often very good Can require more relationship navigation

For most Power BI reporting projects, a Star Schema is usually the preferred starting point because it is simple, intuitive, and works well with Power BI's analytical engine.

11. Filter Direction

Relationships also have a concept called cross-filter direction.

The two common options are:

  • Single
  • Both

With a single-direction relationship:

Customers  →  Sales
Enter fullscreen mode Exit fullscreen mode

Filters generally flow from the Customers table toward Sales.

For example, selecting Kenya in the Customers table can filter the Sales table to show sales from Kenya.

With Both, filtering can flow in both directions.

Although bidirectional filtering can be useful in some situations, it should not be used everywhere because it can introduce ambiguous filter paths and make models harder to understand.

A good model generally uses single-direction filtering where possible.

12. Why Data Modelling Matters

Good data modelling provides several benefits.

1. Accurate Results

Relationships determine how filters move through your model. Incorrect relationships can produce incorrect totals and calculations.

2. Better Performance

A properly designed model can reduce unnecessary data duplication and improve report performance.

3. Easier DAX

Measures become easier to write when tables have clear relationships.

For example:

Total Sales = SUM(Sales[SalesAmount])
Enter fullscreen mode Exit fullscreen mode

You can then analyse Total Sales by customer, product, country, or date without having to manually combine the tables inside every calculation.

4. Easier Report Development

A clean model makes it easier to drag fields into visuals and understand where each field comes from.

5. Easier Maintenance

If the business changes, a well-structured model is easier to update.

13. Common Data Modelling Mistakes

Several mistakes frequently occur when building Power BI models.

Creating One Huge Table

Beginners often join every table together into one massive table.

Although this may seem easier, it can result in:

  • Duplicate information
  • Larger datasets
  • More complicated calculations
  • Poorer performance
  • Difficult maintenance

A Star Schema is often a better approach.

Creating Incorrect Relationships

For example, connecting two tables using columns that do not uniquely identify records can produce incorrect results.

Always understand the meaning of the columns before creating a relationship.

Using Many-to-Many Unnecessarily

Many-to-many relationships can create unexpected results.

Where appropriate, consider introducing a bridge table.

Duplicate Values on the "One" Side

The one side of a one-to-many relationship should normally contain unique values.

For example, if CustomerID appears multiple times in the Customers table, Power BI cannot treat it as a proper one-side key.

Unnecessary Bidirectional Relationships

Setting every relationship to Both can make a model complicated and introduce ambiguous filtering.

Use it only when there is a clear reason.

14. A Practical Power BI Model

Imagine you are analysing a company's sales data.

You might have:

                 DimCustomer
                      |
                      |
DimProduct ───── FactSales ───── DimDate
                      |
                      |
                 DimEmployee
Enter fullscreen mode Exit fullscreen mode

The FactSales table contains:

SaleID
CustomerID
ProductID
DateID
EmployeeID
Quantity
SalesAmount
Cost
Enter fullscreen mode Exit fullscreen mode

The dimension tables contain descriptive information.

You could then create measures such as:

Total Sales = SUM(FactSales[SalesAmount])
Enter fullscreen mode Exit fullscreen mode 
Total Cost = SUM(FactSales[Cost])
Enter fullscreen mode Exit fullscreen mode 
Profit = [Total Sales] - [Total Cost]
Enter fullscreen mode Exit fullscreen mode

Because the relationships are correctly established, you can place Country, Product Category, or Year in a visual and Power BI can automatically filter the sales data appropriately.

Conclusion

Data modelling is the foundation of an effective Power BI report. Understanding the difference between joins, relationships, and schemas is essential for building reliable data models.

Joins are primarily used to combine tables during data preparation, especially in Power Query. Relationships connect tables within the Power BI data model and allow filters and calculations to work across those tables. Schemas describe how the overall model is organised, with the Star Schema being one of the most useful designs for Power BI.

A strong Power BI model usually has a clear fact table, well-defined dimension tables, appropriate one-to-many relationships, unique keys, and sensible filter directions.

The goal is not simply to connect every table together. The goal is to create a model that represents the business logically, makes calculations reliable, and allows users to analyse their data efficiently.

Once you understand joins → relationships → cardinality → fact and dimension tables → Star Schema, you have the foundation needed to build much more professional Power BI reports.

Top comments (0)