Power BI Technical Article: Data Modelling, Relationships & Joins

Introduction

Data modelling is the foundation of every successful Power BI solution. While dashboards and visualisations often receive the most attention, the quality of the underlying data model determines how efficiently reports perform, how easy they are to maintain, and how accurately business insights can be generated.

A well-designed Power BI model enables analysts to create reliable reports, write simpler DAX measures, improve query performance, and scale reporting solutions as business requirements grow. Conversely, a poorly designed model can lead to slow reports, inaccurate calculations, complicated DAX formulas, and maintenance challenges.

This article explores data modelling concepts in Power BI, including schema design, fact and dimension tables, relationships, filter propagation, joins in Power Query, and recommended modelling practices for business intelligence projects.

1. Data Modelling in Power BI

What is Data Modelling?

Data modelling in Power BI is the process of organising tables, defining relationships, and structuring data to support efficient reporting and analysis.
The data model serves as the logical layer between raw data and report visualisations. It defines:

• How tables relate to one another
• How filters travel across tables
• How DAX calculations are evaluated
• How users interact with reports

Why Data Modelling Matters

A well-designed model provides:

1. Faster report performance
2. Simpler DAX calculations
3. Easier maintenance
4. Better scalability
5. Reduced data redundancy
6. Improved user experience

Data Modelling Approaches

A. Flat Table Schema
A flat table is a single spreadsheet-like table where all data, both the actual transactions and the details describing them, is crammed into one place instead of being split up.

Structure

Illustration

Advantages

• Simple to understand: Anyone can view and read the data immediately because it looks just like a standard spreadsheet without any hidden structure.
• Easy initial development: You can build and launch it instantly since you do not have to plan, design, or configure multiple connected tables.
• No relationships required: You avoid the headache of managing primary keys, foreign keys, or complex joins because all data lives in one place.

Disadvantages

• Data duplication: The same descriptive information (like a customer's address) must be repeated manually on every new transaction row.
• Larger model size: Storing the same repeated text strings across thousands of rows causes the file size to bloat unnecessarily.
• Reduced performance: The system slows down heavily as the dataset grows because it has to scan through massive, repetitive rows to find information.
• Difficult maintenance: Managing the data becomes a mess over time because there are no built-in rules to prevent typos or broken records.
• Complex updates: Changing a single piece of info (like a product name) requires finding and modifying every single row where it was ever typed.

Appropriate Use Cases

• Small datasets: It works beautifully for basic tracking like a simple contact list, membership roster, or daily log file.
• Prototypes: It is ideal for mocking up a quick proof-of-concept before investing time into building a complex database structure.
• Quick ad-hoc analysis: It allows analysts to throw data together rapidly for a one-off report or a fast charting session.

As datasets grow, duplicated attributes increase memory consumption and reduce query performance.

B. Star Schema
A star schema consists of one central fact table connected directly to multiple dimension tables. A star schema is like a store receipt:
• ** The Center (Fact Table):Holds the numbers you can count or add up (like sales amounts, quantities, and prices).
• **The Points (Dimension Tables): Hold the details that describe those numbers (like product names, customer names, store locations, and dates).

Structure

Illustration

Advantages

•** Excellent performance:** Direct table relationships allow tools to filter massive datasets at lightning speed.
• Easy DAX calculations: Filters flow naturally to the fact table, keeping formulas clean and simple.
• Easy understanding: The intuitive centered layout makes the model business-friendly and readable for everyone.
• Reduced joins during reporting: Flattened dimension tables drastically cut the number of connections needed for visuals.

Disadvantages

• Some duplicate data in dimensions: Flattened dimension tables repeat text details across rows instead of splitting them into sub-tables.
• Additional modelling effort: Significant upfront work is required in the ETL(Extract, Transform,Load) pipeline to reshape transactional data into facts and dimensions.

Appropriate Use Cases

• Enterprise reporting: Provides a solid, standard foundation for consistent metrics across all corporate business units.
• Dashboards: Delivers fast query response times for instant visual refreshes when users click filters.
• Business intelligence projects: Optimizes data structures for modern BI tools to smoothly slice, dice, and aggregate high-level metrics.
• Data warehouses: Functions as the industry-standard architectural pattern for building efficient data marts and analytical repositories.

Star schemas provide the best performance because Power BI's VertiPaq engine efficiently compresses fact and dimension tables.

C. Snowflake Schema
A snowflake schema is an extension of a star schema where dimensions are further normalised into multiple related tables. Instead of every detail connecting directly to the center, the outer arms are split up and broken down into smaller, sub-connected tables (normalized). For example, instead of storing the city and country together in the "Store" table, the Store table connects to a "City" table, which then connects to a "Country" table.

Structure

Illustration

Advantages

• Reduced redundancy: Splits dimensions into sub-tables to replace repetitive text with compact ID numbers.
• Better normalisation: Strictly follows relational database principles to cleanly isolate data by its functional relationships.
• Improved data integrity: Keeps a single source of truth so changes only require updating one single row.

Disadvantages

• More relationships: Creates a dense web of table connections that developers must map and manage.
• Increased complexity: The branched table structure makes the model harder for business users to visually navigate.
• Slower performance: Requires multiple database joins to retrieve data, which can visibly drag down report loading times.
• More difficult DAX: Long filtering paths force complex formula writing to control data context accurately.

Appropriate Use Cases

• Highly normalised source systems: Perfectly mirrors transactional databases where data is already deeply nested at the source.
• Complex enterprise models: Organizes massive corporate warehouses with deeply layered, multi-level business hierarchies.

Additional joins increase complexity and may reduce performance compared to a star schema.

2. Fact Tables and Dimension Tables

A fact table stores measurable business events. These include:

• Sales Amount
• Quantity Sold
• Profit
• Cost
• Revenue

Example:

Characteristics

• Large volume of records: Continuously captures individual transactions, quickly growing to hold millions or billions of rows.
• Contains numeric measures: Stores quantitative values—like revenue or quantity—intended for mathematical aggregation in reports.
• Contains foreign keys: Holds unique key identifiers that link each row directly to surrounding dimension tables.

Dimension Table

A dimension table stores descriptive information.

Example:

Characteristics

• Descriptive attributes: Stores qualitative text context—like names or categories—that defines raw numbers.
• Supports filtering and grouping: Enables users to slice, dice, and organize metrics across report visuals.
• Contains primary keys: Holds unique column identifiers to cleanly anchor connections with fact tables.

Grain (Granularity)

Granularity defines the lowest level of detail stored in a fact table.

Examples:

The grain should be defined before modelling begins because it affects all relationships and calculations.

Practical Example

Retail Sales Model:

A report can answer:

• Sales by customer
• Sales by region
• Sales by product
• Sales by month

using a single fact table connected to multiple dimensions.

3. Relationships in Power BI

A relationship is a logical connection between two tables that allows filters and calculations to work across the model.
Without relationships, tables operate independently.

One-to-Many Relationship (1:*)

One unique value in one table relates to many rows in another.

Example

Use Cases

• Customer to Sales
• Product to Orders
• Date to Transactions

It is the most commonly used relationship type in Power BI.

One-to-One Relationship (1:1)

Each value appears only once in both tables.

Example

Use Cases

• Security tables
• Supplementary attributes

This relationship should be used sparingly.

Many-to-Many Relationship (:)

Multiple rows on both sides contain matching values.

Example

Use Cases

• Budget allocations
• Complex business scenarios

It is better to avoid this data relationship unless it is really necessary.

Primary Keys and Foreign Keys

Primary Key

A unique identifier column in a table where every row contains a distinct value and no blanks ensuring each record can be instantly found and targeted.

Example:

Foreign Key
References another table. Is a column in one table that links to the primary key of another table, creating the connection that lets you slice and dice your metrics.

Example:

CustomerID is unique in DimCustomer but appears many times in FactSales because a customer can make multiple purchases.

Referential Integrity

A database rule ensuring that relationships between tables remain valid and consistent and ensures every foreign key matches an existing primary key.

Good:

Bad:

Active and Inactive Relationships

Example:

FactSales contains:
• Order Date
• Ship Date
Both link to DimDate.

Only one relationship can be active.

Inactive relationships can be activated using:DAX

4. Filter Direction

Defines the path that data filtering travels between connected tables. Also referred to as Filter propagation.

Single Direction Filtering

Recommended approach.

Laptop

filters FactSales to show only laptop sales.

Advantages

• Excellent performance: Keeps query pathways simple and direct, minimizing memory overhead across massive datasets.
• Prevents ambiguous paths: Forces data filters to move in one logical direction, eliminating conflicting filter loops.
• Cleaner DAX calculations: Eliminates unintended side-effects, making metrics highly predictable and easy to debug.
• Aligns with best practice: Matches the native design of analytical storage engines for maximum scalability.

Bidirectional Filtering

Filtering works both ways.

Advantages

• Simplifies multi-table filtering: Allows filters to flow seamlessly both ways, making it easy to filter a dimension based on facts or another dimension.
• Reduces complex DAX code: Eliminates the need to write advanced functions like CROSSFILTER or CALCULATE to force backward filtering.
• Enables complex cross-filtering: Ensures that selecting an item in one slicer instantly hides irrelevant options in another slicer across different tables.

Disadvantages

• Degrades query performance: Forces the engine to evaluate two-way filter paths, significantly slowing down report loading times.
• Causes ambiguous relationships: Generates circular filter loops across your model that can return inaccurate or unpredictable data.
• Inflates memory consumption: Demands high CPU and RAM usage because the reporting engine must constantly sync table states.
• Triggers unexpected visual filtering: Unintentionally filters unrelated visuals and slicers, creating a confusing end-user experience.
• Triggers unexpected visual filtering: Unintentionally filters unrelated visuals and slicers, creating a confusing experience for the end-user.

Example Problem

Bidirectional filtering can create multiple filter paths leading to unexpected results therefore, use Single Direction by default and both only when required.

5. Joins in Power Query

A join combines data from two tables using a common column.It is an operation that temporarily combines rows from two or more tables based on a related column between them, typically matching a foreign key in one table to a primary key in another, allowing you to query and view the combined data as a single result.

Example Tables:

Customers

Orders

Left Outer Join

Returns all rows from the left table along with the matching rows from the right table. If no match exists, it fills the missing right-side columns with NULL values

Output

Illustration

Right Outer Join

Returns all rows from the right table along with the matching rows from the left table. If no match exists, it fills the missing left-side columns with NULL values.

Output

Full Outer Join

Returns all rows from both tables, combining records regardless of where a match is found. If rows on either side do not match, it fills the missing values on the opposite side with NULL values.

Output

Inner Join

Returns only the rows that have matching values in both tables. Any row without a corresponding match on either side is completely excluded from the final result.

Output

Left Anti Join

Returns rows from the left table only if they have no matching value in the right table. It acts as a exclusion filter, completely dropping any rows that successfully find a match.

Output

Right Anti Join

Returns rows from the right table only if they have no matching value in the left table. It acts as an exclusion filter, completely dropping any rows that successfully find a match on the left side.

Output

Visual Summary of Join Types

6. Power Query Joins vs Power BI Relationships

Power Query Merge Example

Customers and Orders become:

A single table is produced.

Relationship Example

Keep:

separate and connect through CustomerID.
No physical combination occurs.

When to Use a Merge

Use when:

• Lookup values needed
• Small supporting tables
• Data cleansing requirements

When to Use Relationships

Use when:

• Building analytical models
• Following star schema practices
• Supporting scalable reporting

Why Excessive Merging is a Problem

Excessive merging:

• Creates flat tables
• Increases redundancy
• Enlarges model size
• Reduces flexibility

Keeping fact and dimension tables separate is usually preferable.

7. Recommended Power BI Model

For a typical business intelligence project, the Star Schema is the recommended design.

Recommended Structure

Recommended Relationships

• One-to-Many relationships
• Single-direction filtering
• Active relationships where possible
• Surrogate primary keys

Why Star Schema is Recommended

1. Better PerformancePower BI's storage engine performs best with star-schema structures.
2. Simpler DAXMeasures become easier to write and understand.
Example:

3. ScalabilityAdditional dimensions can be added without redesigning the model.
4. Easier MaintenanceChanges remain isolated within dimensions.
5. Improved Report CreationBusiness users can easily understand the model.
6. Better Filter PropagationFilters flow naturally from dimensions to the fact table.
7. Reduced ComplexityCompared with snowflake schemas, fewer relationships mean easier troubleshooting and improved readability.

Conclusion

Data modelling is one of the most important activities in Power BI development. A strong model directly influences report performance, scalability, maintainability, and analytical accuracy. While flat tables may be suitable for small datasets and snowflake schemas may be useful in specialised situations, the star schema remains the industry standard for most business intelligence projects.

Understanding fact and dimension tables, relationship cardinalities, filter propagation, and join operations allows developers to build reliable analytical solutions. Additionally, recognising the difference between Power Query joins and Power BI relationships ensures that data is prepared efficiently while preserving an optimised analytical model.

In practice, the most effective Power BI solution typically consists of a central fact table connected to multiple dimension tables through one-to-many relationships, using single-direction filtering and a well-structured star schema. This design provides the optimal balance between performance, simplicity, scalability, and maintainability, making it the preferred approach for modern business intelligence environments.