Data Modelling, Relationships And Joins In Power BI

Introduction

Power BI is a business intelligence and data visualization platform used to transform raw data into meaningful information for decision making. However, creating effective reports does not only depend on attractive charts and dashboards. The underlying data model is equally important. A well designed data model determines how tables interact, how filters are applied, how DAX calculations behave, and how efficiently Power BI processes queries.

Data modelling in Power BI involves organizing tables, defining relationships between them, and establishing how data should interact within a report. Common modelling approaches include the flat table, star schema, and snowflake schema. In addition, Power BI provides relationships in the data model and joins in Power Query for connecting data.
Understanding these concepts is essential because poor modelling can result in incorrect calculations, duplicated information, slow reports, ambiguous filter paths, and difficult-to-maintain solutions. This article discusses data modelling, fact and dimension tables, relationships, filter direction, Power Query joins, and the differences between Power Query merges and Power BI relationships.

Data modelling in power BI

Data modelling in Power BI is the process of organizing data from one or more sources into a structured model that allows users to analyze and visualize information effectively. It involves deciding which tables should be created, how they should be related, which columns should be keys, and how filters should move between tables. For example, a retail business may have information about customers, products, dates, locations, and sales. Instead of keeping all this information in one large table, the data can be separated into logical tables and connected using relationships.

A good data model is important because it Makes DAX calculations easier to write and understand, Improves report and query performance, Reduces unnecessary data duplication, Makes the model easier to understand, allows reports to scale as data increases, Makes troubleshooting easier, Supports accurate filtering and aggregation and Improves long-term maintainability while a poor data model is a disadvantage because it can produce incorrect totals, unnecessarily large datasets, complicated DAX formulas and ambiguous relationships.

Flat table

A flat table is a single, large data table that combines all your information such as sales transactions, product names, and customer details into one wide row structure, for example

customer ID │ Date │ Customer │ Product │ Sales │ Category│ Location │ Quantity │

1001 | 01/09/2026 | Alice | Laptop | Electronics | Nairobi| 1 | 80,000
1002| 01/09/2026 |John| Phone |Electronics| Kisumu| 2| 60,000|

A flat table can be suitable for a small, simple dataset where the data has relatively few rows. However, for larger business intelligence solutions, a dimensional model is generally more appropriate
A flat table is Simple to understand, easy to import and start analyzing, requires few or no relationships, appropriate for small dataset, and convenient for simple dashboards. A flat table also has limitations in that it creates data redundancy ,the same customer, product, or category information may be repeated many times the table can become very wide, changes to descriptive information may need to be repeated across many records, it becomes difficult to maintain as the dataset grows, and it may increase model size and reduce the advantages of dimensional modelling.

Star schema

A star schema is a data modeling technique in Power BI where a central fact table connects directly to multiple surrounding dimension tables, forming a star like structure. It consists of a central fact table surrounded by dimension tables, the structure resembles a star because the fact table is positioned at the center while dimensions surround it. Dimension tables support filtering and grouping, while fact tables support summarization. Star schema is highly suitable for business intelligence projects involving sales, finance, inventory, human resources, healthcare, marketing, and other analytical workloads.

It is generally the preferred approach for Power BI models because it separates descriptive information from measurable business events

for example:

Fact Sales: customer ID, Date Key ,Customer Key ,Product Key ,Location Key
Quantity, Sales Amount,

Dim Customer: Customer Key, Customer Name, Gender, Age group

Dim Product: Product Key, Product Name, Category, Brand
Dim Date: Date Key, Date, Month, Quarter, Year
Dim Location :Location Key, County, Region

Advantages of a star schema

• Provides a clear and understandable model.

• Reduces unnecessary duplication.

• Makes DAX measures easier to create.

• Provides efficient filter propagation.

• Supports scalable reporting.

• Makes the Model View easier to understand.

• Works well with Power BI's analytical engine.
  The disadvantages of a star schema are as follows:

• Requires more planning than a flat table.

• May require Power Query transformations.

• Poorly designed keys can cause relationship problems.

• Users need to understand fact and dimension concepts.

Snowflake schema

A snowflake schema is a data modeling structure where dimension tables are split into multiple related sub dimension tables forming a pattern that resembles a snowflake .It is an extension of the star schema where dimension tables are further divided into related tables. Snowflake schemas may be appropriate when dimensions have complex hierarchical structures or when maintaining normalized dimension data is important. However, Microsoft notes that Power BI models generally benefit from star-schema principles, and snowflake dimensions may sometimes not be normalized to produce a simpler model table.

Advantages

• Reduces duplication within dimensions.

• Can represent complex hierarchical structures.

• Can be useful when dimensions contain multiple levels.

• May be appropriate when the source system is already highly normalized.

Disadvantages

• Creates more tables and relationships.

• Makes the model more complicated.

• Can make report development less intuitive.

• Can create longer filter paths.

• May require more complex DAA

Fact tables and Dimension tables

Fact tables

A fact table stores measurable business events. It normally contains numeric values that can be aggregated together with foreign keys that identify the related dimensions. It stores quantitative, numerical data and measurable business events like sales, orders, or transactions. Fact tables anwser questions like how many, how much, how often and what was the total

Dimension tables

Dimension tables contain descriptive information used to filter, group, and analyze facts. Examples include: Dim Customer, Dim Product, Dim Date, Dim Location. Dimensions anwser questions like who, where , what, when and which category

Grain or granularity of a fact table

The grain describes exactly what one row in a fact table represents.For example, the grain of Fact Sales might be, One row represents one product sold in one order. This distinction is important because all measures and relationships depend on the level of detail stored in the fact table. A consistent grain prevents incorrect aggregations and helps ensure that DAX calculations produce reliable results. Microsoft specifically recommends that fact tables load data at a consistent grain.

Relationships in power BI

Relationships in a Power BI data model link two separate tables together using a common column or key, allowing filters to flow and data to combine correctly in your reports. They are important business information is often distributed across several tables.

One to One

A one-to-one relationship occurs when each record in one table corresponds to exactly one record in another table. It may be useful when information has been separated into two tables for security, organization, or specific modelling requirements.

Customer ID 1 ---------- 1 Customer ID

One to many

A one-to-many relationship occurs when one record in one table can be related to many records in another table. For example One customer can make many purchases. This is the most common relationship in a star schema. It is normally used between a dimension table and a fact table. It should not be used if the supposed one side contains duplicate values the key on the one side must be unique

Dim Customer                 Fact Sales

Customer ID  1  ----------    Customer ID

For example:

Dim Customer
C001 - Mary

Fact Sales
C001 - Order 101
C001 - Order 102
C001 - Order 103

Many to Many

A many-to-many relationship occurs when multiple records in one table can correspond to multiple records in another table. For example, one student may register for many courses, while one course may have many students. Many to many relationships are useful when the business situation genuinely contains many-to-many relationships. They should not be used to solve duplicate key problems. Where possible, a bridge table and a clearer star schema should be considered. Primary key uniquely identifies each record in a table while a foreign key is a column that refers to a key in another table.
Student ID -------- Course ID

Referential Integrity

Referential integrity means that relationships between tables are logically consistent. For example, a Customer ID appearing in Fact Sales should normally correspond to a valid Customer ID in Dim Customer. If a fact record contains a Customer ID that does not exist in the customer dimension, it may result in unmatched or unexpected data during analysis. Good data preparation should therefore identify missing keys, duplicates, and invalid references before building the final model.

Active and inactive relationships

An active relationship is the relationship Power BI automatically uses when applying filters between tables while a inactive relationship exists in the model but is not automatically used for filtering. It can be activated within a DAX calculation when required. A common example involves dates, Fact Sales table might contain: Order Date, Ship Date, Delivery Date. A single Date dimension may therefore have multiple relationships with Fact Sales, but normally only one can be active for automatic filtering at a time. Inactive relationships are useful when a report needs to analyze the same fact table using different date roles.

Filter direction

Filter direction determines how filters move between related tables. With single-direction filtering, filters normally move from the dimension table toward the fact table while bidirectional filtering allows filters to move in both directions. single direction filtering is normally prefered in star schema because it provides predictable filter propagation. Possible problems associated with bidirectional filtering are Ambiguous filter paths, Unexpected filtering behaviour. More complicated models, Difficulty troubleshooting calculations and Potential performance issues, Therefore single direction filtering is the most prefered.

Joins in power query

A join combines information from two tables based on one or more matching columns. In Power Query, this is performed using Merge Queries.

A Left Outer Join keeps all records from the left table and matching records from the right table.
Right Outer Join keeps all records from the right table and matching records from the left table.
Full Outer Join keeps all records from both tables, whether or not they have a match.
An Inner Join keeps only records that have a match in both tables.
Left Anti Join returns records that exist in the left table but do not have a match in the right table.
A Right Anti Join returns records that exist in the right table but do not have a match in the left table.

| Join Type | Records Retained | Example |
| Left Outer | All Customers + matching Orders | Find all customers, including those without orders |
| Right Outer | All Orders + matching Customers | Find all orders, including unmatched orders |
| Full Outer | All Customers and Orders | Find all matched and unmatched records |
| Inner | Only matching records | Find customers who placed orders |
| Left Anti | Customers without Orders | Find customers who never placed an order |
| Right Anti | Orders without Customers | Find orders without matching customer |

Power query joins vs power BI relationships

Power Query merge combines information during the data preparation stage. For example, Customers and Orders can be merged using Customer ID so that customer information becomes available in the resulting query.

Customers + Orders
↓
Power Query Merge
↓
Combined Query

The merge can physically bring columns from one table into another.

A relationship is created during the data modelling stage.

Dim Customer
|
| 1:*
|
Fact Sales

The tables remain separate.

A relationship does not physically combine their columns. Instead, it tells Power BI how the tables are connected and how filters should move between them.
Their differences include: Power query merge is a data preparation stage, Combines columns/data ,Produces a transformed query, Uses join types, Can increase table size, Useful for enriching data while a power BI relationship is a Data modelling stage, Connects separate tables, Produces a model relationship, Uses cardinality and filter direction, Keeps tables separate, Useful for analysis and filtering
A merge can be appropriate when: A column is required directly in another table, Data sources need to be cleaned or consolidated, The information logically belongs in the same table while a relationship is suitable when relationship is preferable when tables represent different business entities or roles.

Reccommended power BI model

For a typical business intelligence project, I would recommend a star schema. The recommended model would contain one or more central fact tables supported by descriptive dimension tables. I would also recommend to use one-to-many relationships between dimensions and fact tables.

For example:

Dim Customer 1 → * Fact Sales
Dim Product 1 → * Fact Sales
Dim Date 1 → * Fact Sales
Dim Location 1 → * Fact Sales
I would also use single-direction filtering from dimensions to facts.

Dim Product
|
↓
Fact Sales

This provides predictable filter propagation and reduces the possibility of ambiguous relationships. Bidirectional filtering would only be introduced when a genuine business requirement makes it necessary.
The following are the reasons why I would recommend a star schema:

1.DAX simplicity Measures can be created against the fact table while dimensions provide the filtering context.
For example: Total Sales = SUM(Fact Sales[Sales Amount])
A user can then analyze this measure by Product, Customer, Date, or Location.

2.Scalability New products, customers, locations, and transactions can be added without redesigning the entire model.

3.Reduced Redundancy Customer and product information does not need to be repeated for every transaction.

4.Maintainability Changes to dimension information can be made in one location rather than across thousands or millions of fact records.

5.Filter Propagation The dimension-to-fact relationship provides a predictable filtering structure.

6.Performance Separating descriptive dimensions from transactional facts can make the model efficient and reduce unnecessary duplication.

Conclusion

In conclusion, effective data modelling, relationships, and joins are essential for building reliable Power BI reports. A star schema with clear fact and dimension tables, appropriate relationships, and mainly single-direction filtering provides better performance, simpler DAX, and easier maintenance. Proper use of Power Query joins also helps prepare and combine data efficiently for analysis.