Introduction
Power BI is used to analyse data and turn it into useful reports and dashboards. For the reports to produce accurate and meaningful results, the underlying data needs to be organized properly. This is where data modelling, relationships, and joins become important.
A good data model makes it easier to create DAX calculations, improve report performance, manage large datasets, and maintain a Power BI project as it grows. This article explains the main approaches to data modelling, the role of fact and dimension tables, relationships and filter directions, and the different types of joins available in Power Query.
Data Modelling in Power BI
Data modelling in Power BI is the process of organizing tables and defining how they are connected. A data model determines how information from different tables works together when creating reports, calculations, filters, and visualizations.
For example, a retail business may store customers, products, dates, locations, and sales transactions in separate tables. These tables can be connected using common fields such as CustomerID, ProductID, and DateKey.
A simple model can be represented as follows:
A well-designed model can make DAX calculations easier, improve query performance, reduce unnecessary duplication, and make the report easier to maintain.
There are three common ways of organizing data for analysis: flat tables, star schemas, and snowflake schemas.
Flat Table
A flat table stores most of the required information in a single table.
For example:
Information is often repeated. If examples one makes 1,000 purchases, his name, region, and other customer information may appear many times. This creates data redundancy and can increase the size of the model.
Advantages
Easy to understand.
Simple to create.
Use for small datasets.
Requires fewer relationships.
Suitable for simple reports.
Disadvanatges
Creates repeated information.
Can become very large.
More difficult to maintain.
Can make the model less flexible.
Not ideal for complex business intelligence solutions.
A flat table can be appropriate for a small report where the data is already prepared and there are relatively few records.
Star Schema
A star schema separates the main business events from the descriptive information used to analyse those events.
The centre of the model is normally a fact table, while dimension tables surround it.
Advantages
Clear and easy to understand.
Usually requires fewer relationships than a highly normalized model.
Makes filtering and reporting straightforward.
Makes DAX calculations easier to manage.
Reduces unnecessary duplication.
-
Scales well for business intelligence projects.
Disadvantages
Requires proper planning.
More tables are involved than in a flat table.
The designer needs to understand keys and relationships.
Poorly designed dimensions can still cause problems.
The star schema is appropriate for sales systems, financial reporting, inventory systems, customer analytics, and many other business intelligence applications.
It is also a good choice when several reports need to use the same customers, products, dates, or locations.
Snowflake Schema
A snowflake schema is similar to a star schema, but some dimension tables are divided into additional related tables.
For example, instead of storing a product's category directly in DimProduct, category information can be placed in a separate DimCategory table.
Advantages
Reduces some data duplication.
Can represent complex hierarchies.
Can be useful when dimensions contain large amounts of related information.
Can fit well with highly normalized source systems.
Disadvantages
Creates more tables.
Requires more relationships.
Makes the model harder to understand.
Can make filtering more complicated.
Can increase model maintenance requirements.
A snowflake schema may be useful when the additional separation provides a genuine benefit. However, unnecessarily splitting dimensions can make a Power BI model more complicated than it needs to be.
Fact Tables and Dimension Tables
Fact and dimension tables are the main building blocks of a star schema
A fact table stores measurable business events.
Examples include:FactSales,FactOrders,FactTransactions,FactInventory
Dimension Tables contain descriptive attributes that provide context for the facts.
Common dimensions include:DimCustomer,DimProduct,DimDate,DimLocation.
Practical Fact-Dimension Example
Consider an online retail company.
The central table is:
FactSales
It records each sale.
It connects to:
DimCustomer — who purchased?
DimProduct — what was purchased?
DimDate — when was it purchased?
DimLocation — where was the sale made?
The model can therefore answer questions such as:
What were total sales?
Which product generated the most revenue?
Which county generated the highest sales?
How much did each customer spend?
What were sales during 2026?
Which product category performed best?
This is the main strength of the star schema: dimensions provide the context while the fact table provides the measurable events.
Relationships in Power BI
A relationship connects two tables using related columns.
Relationships are necessary because business information is usually stored in multiple tables rather than one large table.
For example:
DimCustomer[CustomerID]
can be connected to:
FactSales[CustomerID]
When a customer is selected in a report, the relationship allows the selection to affect the relevant sales records. Power BI relationships propagate filters between connected tables.
Relationship Cardinality
Cardinality describes how values in one table relate to values in another.
Power BI supports four main cardinality types:
One-to-One 1:1
One-to-many 1:*
Many-to-many :
One-to-One Relationship (1:*)
A one-to-one relationship means each value in one table corresponds to only one value in another table.
Example:
Product SkU may appear once in both:
Product table
Productcategory table
When to use it
It can be useful when information belonging to one entity has been deliberately separated into two tables.
One-to-Many Relationship (1:*)
This is the most common relationship in a star schema.
Example:
DimCustomer[CustomerID] 1 → * FactSales[CustomerID]
A customer appears once in the customer dimension but can appear many times in the sales table.
When to use it
Use it when one record in the first table can be associated with multiple records in the second table.
This is normally the preferred relationship between dimensions and fact tables.
Many-to-Many Relationship (:)
A many-to-many relationship occurs when values in both tables can occur multiple times.
For example:
One student can belong to several courses.
One course can have many students.
Direct many-to-many relationships can be useful, but they can also make filter propagation harder to understand.
A bridge table can provide a clearer solution.
For example:
DimStudent
↓
StudentCourse
↓
DimCourse
The bridge table records the combinations between students and courses.
Keys and Referential Integrity
Relationships normally depend on matching key columns.
Primary Key
A primary key uniquely identifies a record.
Example:
DimCustomer[CustomerID]
could contain:
C001
C002
C003
Each value appears only once.
Foreign Key
A foreign key refers to a record in another table.
In FactSales, the CustomerID may appear repeatedly:
C001
C001
C002
C001
This is because one customer can make many purchases.
Referential Integrity
Referential integrity means that foreign-key values should correctly correspond to valid records on the related dimension side.
For example, if FactSales contains CustomerID C001, the corresponding customer should normally exist in DimCustomer.
If the dimension side is supposed to be the "one" side of a relationship, its values must be unique. Power BI can detect relationship problems when the supposed one-side column contains duplicates.
Active and Inactive Relationships
Power BI relationships can be active or inactive.
An active relationship is the default relationship used for filtering and reporting.
Inactive relationships can be useful when two tables have multiple possible relationships.
For example, a Sales table may contain:
OrderDate
ShipDate
DeliveryDate
All three dates could relate to a Date table, but normally only one relationship may be active at a time for a particular role.
Inactive relationships can then be activated in specific DAX calculations when required.
Filter Direction
Filter direction determines how filters move between related tables.
Single Direction
In a typical star schema:
DimProduct → FactSales
A product selection filters the sales table.
For example, selecting:
Product Category = Electronics
can filter the sales records belonging to Electronics products.
Single-direction filtering is commonly used because it makes the filter path easier to understand.
Bidirectional Filtering
Bidirectional filtering allows filters to travel in both directions.
For example:
DimProduct ↔ FactSales
A filter can travel from the product table to sales and, depending on the model, from sales back toward the product table.
Bidirectional filtering can solve certain modelling problems, but excessive use can create:
Ambiguous filter paths
Unexpected results
More complex models
Potential performance problems
Power Query Joins
Power Query joins are used during the data preparation stage.
In Power Query, a Merge Queries operation combines information from two queries using matching columns.
For example:
Sales
CountryID Units
1 20
2 15
3 30
Countries
ID Country
1 Kenya
2 Uganda
3 Tanzania
The tables can be merged using:
Sales[CountryID] = Countries[ID]
After the merge, the Country column can be expanded into the Sales query.
Power Query supports several join types, including Left Outer, Right Outer, Full Outer, Inner, Left Anti, and Right Anti.
Left Outer Join
A Left Outer Join keeps every row from the left table and adds matching information from the right table.
Sales: This table includes the fields Date, CountryID, and Units. CountryID is a whole number value that represents the unique identifier from the Countries table.
Countries: This table is a reference table with the fields ID and Country. The ID field represents the unique identifier for each record.
n this example, you merge both tables, with the Sales table as the left table and the Countries table as the right one. The join is made between the following columns.
Field from the Sales table Field from the Countries table
CountryID ID
The goal is to create a table like the following, where the name of the country appears as a new Country column in the Sales table as long as the CountryID exists in the Countries table. If there are no matches between the left and right tables, a null value is the result of the merge for that row. In the following image, this null value is shown to be the case for CountryID 4, which was brought in from the Sales table.
To do a left outer join:
Select the Sales query, and then select Merge queries.
In the Merge dialog box, under Right table for merge, select Countries.
In the Sales table, select the CountryID column.
In the Countries table, select the ID column.
In the Join kind section, select Left outer.
Select OK.
From the newly created Countries column, expand the Country field. Don't select the Use original column name as prefix check box.
After performing this operation, you create a table that looks like the following image.
Right Outer Join
One of the join kinds available in the Merge dialog box in Power Query is a right outer join, which keeps all the rows from the right table and brings in any matching rows from the left table
Examples :
Sales: This table includes the fields Date, CountryID, and Units. The CountryID is a whole number value that represents the unique identifier from the Countries table.
Countries: This table is a reference table with the fields ID and Country. The ID field represents the unique identifier for each record.
To do a right outer join:
Select the Sales query, and then select Merge queries.
In the Merge dialog box, under Right table for merge, select Countries.
In the Sales table, select the CountryID column.
In the Countries table, select the ID column.
In the Join kind section, select Right outer.
Select OK.
From the newly created Countries column, expand the Country field. Don't select the Use original column name as prefix check box.
After performing this operation, you create a table that looks like the following image.
Full Outer Join
One of the join kinds available in the Merge dialog in Power Query is a full outer join, which brings in all the rows from both the left and right tables.
Sales: This table includes the fields Date, CountryID, and Units. CountryID is a whole number value that represents the unique identifier from the Countries table.
Countries: This table is a reference table with the fields ID and Country. The ID field represents the unique identifier for each record.
The goal is to create a table like the following, where the name of the country appears as a new Country column in the Sales table. Because of how the full outer join works, all rows from both the left and right tables are brought in, regardless of whether they only appear in one of the tables.
To perform a full outer join:
Select the Sales query, and then select Merge queries.
In the Merge dialog box, under Right table for merge, select Countries.
In the Sales table, select the CountryID column.
In the Countries table, select the ID column.
In the Join kind section, select Full outer.
Select OK
Best use
A full outer join is useful when the objective is to identify and retain records from both datasets, including unmatched records.
Inner Join
An Inner Join keeps only records that have matching values in both tables.
For example:
Customers:
C001
C002
C003
Orders:
C001
C002
C004
The inner join returns:
C001
C002
C003 and C004 are removed because they do not have matching records in both tables.
Best use
An inner join is useful when only records with matching information in both datasets are required.
Left Anti Join
A Left Anti Join returns records that exist in the left table but do not have a matching record in the right table.
Example:
Customers:
C001
C002
C003
Orders:
C001
C002
The left anti join returns:
C003
Practical use
This can be used to identify customers who have never placed an order.
It is also useful for data-quality checks and identifying missing relationships.
Right Anti Join
A Right Anti Join returns records from the right table that do not have matching records in the left table.
For example:
Customers:
C001
C002
Orders:
C001
C002
C003
A right anti join returns:
C003
This could identify an order containing a CustomerID that does not exist in the Customers table.
Practical use
It is particularly useful for finding missing or unmatched records.
Power Query Joins vs Power BI Relationships
Although joins and relationships connect data, they perform different jobs.
Power Query Merge
A merge is performed during data preparation.
It combines data from two queries and can add columns from one query into another.
For example:
Sales + Customer
can produce a query containing:
SalesID
CustomerID
SalesAmount
CustomerName
After the merge, the resulting query can be loaded into the Power BI model.
Microsoft's Power Query documentation demonstrates that Merge Queries can be used to combine queries and then expand columns from the merged table.
Power BI Relationship
A relationship is created in the data model.
Instead of physically putting all columns into one table, the tables remain separate.
For example:
DimCustomer
↓
FactSales
The relationship allows filters to move between the tables.
Power BI relationships therefore support the analytical model, while Power Query merges are mainly used for preparing and shaping the data.
When Should You Merge?
A Power Query merge is appropriate when:
You need to add a column from another query.
Data needs to be cleaned or transformed before loading.
You want to resolve a lookup during data preparation.
The combined result makes the final model simpler.
The information logically belongs together.
For example, if a product ID needs to be converted into a product category before the data reaches the model, a merge can be appropriate.
When Should You Use a Relationship?
A relationship is normally preferable when:
Tables represent different business entities.
A fact table needs several dimensions.
The same dimension will be reused across multiple reports or fact tables.
You want dimensions to filter facts.
You want to maintain a star-schema structure.
The data should remain logically separated.
For example:
DimProduct → FactSales
is generally better than repeatedly copying product information into every sales record.
Problems with Excessive Merging
Although merging can be useful, excessive merging can create a very large and complicated table.
For example, instead of having:
DimCustomer
DimProduct
DimDate
FactSales
a developer might merge everything into one huge sales table.
This can result in:
Repeated customer information.
Repeated product information.
Increased redundancy.
Larger datasets.
More difficult maintenance.
Less flexible modelling.
A star schema avoids much of this unnecessary repetition by keeping dimensions separate from facts.
Recommended Power BI Model
For a typical business reporting application, the recommended approach is a star schema.
A suitable model could be:
DimDate
↓
FactSales
↑
DimCustomer
along with:
DimProduct → FactSales ← DimLocation
The fact table contains transactions and numerical measures, while dimensions contain descriptive attributes.
Why Star Schema?
- Performance
A well-structured star schema provides a model that is optimized for analytical queries. Microsoft specifically describes star schema as an important design approach for Power BI semantic models optimized for performance and usability.
- DAX Simplicity
Measures can be created in the fact table while dimensions provide the filtering context.
For example:
Total Sales = SUM(FactSales[SalesAmount])
A user can then place:
Year from DimDate
Category from DimProduct
County from DimLocation
into a visual while Power BI uses the relationships to filter FactSales.
- Readability
A model with clearly named fact and dimension tables is easier for developers and report users to understand.
- Scalability
New dimensions can be added without rebuilding the entire model.
For example, the organization could later add:
DimEmployee
or
DimSupplier
while retaining the central sales fact table.
- Maintainability
Separating business entities reduces duplication and makes it easier to update descriptive information.
- Filter Propagation
Relationships provide controlled paths for filters to travel from dimensions to facts.
- Relationship Simplicity
The most common pattern is a one-to-many relationship:
Dimension 1 → * Fact
This makes the model easier to understand than a network containing many unnecessary many-to-many or bidirectional relationships.
Practical Design Example
Consider a supermarket reporting system.
The model could contain:
FactSales
SalesID
DateKey
CustomerID
ProductID
StoreID
Quantity
SalesAmount
Cost
DimProduct
ProductID
ProductName
Category
Brand
DimCustomer
CustomerID
CustomerName
Gender
CustomerType
DimDate
DateKey
Date
Month
Quarter
Year
DimStore
StoreID
StoreName
County
Region
Relationships:
DimProduct[ProductID] 1 → * FactSales[ProductID]
DimCustomer[CustomerID] 1 → * FactSales[CustomerID]
DimDate[DateKey] 1 → * FactSales[DateKey]
DimStore[StoreID] 1 → * FactSales[StoreID]
This model can support reports such as:
Total sales by product category
Sales by county
Monthly revenue
Sales per customer
Profit by store
Best-performing products
The model remains understandable because each table has a clear purpose.
- Common Modelling Mistakes
Several mistakes can reduce the quality of a Power BI model.
- Using One Huge Table
This creates unnecessary duplication and can make the model difficult to maintain.
- Incorrect Cardinality
For example, treating a column containing duplicate customer IDs as the "one" side of a relationship can produce errors or incorrect results.
Power BI requires the one side of a relationship to contain unique values.
- Excessive Many-to-Many Relationships
Many-to-many relationships can create more complex filtering behaviour. A bridge table can often provide a clearer design.
- Excessive Bidirectional Filtering
Using Both everywhere can create ambiguous filter paths and make the model harder to understand.
- Ignoring Fact Table Grain
A fact table should have a clearly defined meaning for each row.
- Unnecessary Merging
Not every table needs to be merged. Maintaining separate fact and dimension tables is often preferable.
Conclusion
Data modelling is fundamental to successful Power BI development. The way tables are organized directly affects reporting accuracy, DAX calculations, performance, scalability, and maintainability.
A flat table is simple and can be useful for small datasets, but it becomes less suitable as data grows. A snowflake schema can reduce redundancy but introduces additional tables and relationships. For most Power BI analytical models, the star schema provides the best balance of simplicity, performance, scalability, and usability.
Fact tables should contain measurable business events at a clearly defined grain, while dimension tables should contain descriptive attributes. Relationships connect these tables and control filter propagation. One-to-many relationships are particularly important because they naturally represent the relationship between dimensions and facts.
Power Query joins serve a different purpose. They are mainly used to prepare and combine data before it reaches the model. Left, right, full, inner, and anti joins allow developers to control which records are retained during data transformation.
Top comments (0)