Power BI Technical Article : Data Modelling, Relationships & Joins

Introduction

In today's data-driven business environment, organizations collect large amounts of information from sales systems, customer databases, financial systems, spreadsheets, and operational applications. Having lots of data is not enough, though. Organizations need to turn it into meaningful information that supports better decisions.

Power BI is a business intelligence and data visualization platform developed by Microsoft. It lets you connect to different data sources, clean and transform data, build data models, create calculations, and present insights through interactive reports and dashboards.
Power BI can connect to Excel workbooks, CSV files, databases, cloud services, and other business applications.

Power Query is then used to clean and transform the data before it is loaded into the Power BI data model.

Transforming data and building visuals are only part of the process. How the data is structured in the model is just as important.

Why Is Data Modelling Important?

Data modelling is the process of organizing tables, defining relationships between them, and creating a structure that allows data to be analyzed correctly. A good model matters because of:

• Reporting: users can easily slice and filter data using dimensions.
• DAX calculations: measures become easier to write and understand.
• Performance: a good model reduces unnecessary duplication and supports efficient storage and querying.
• Scalability: the model can accommodate growing transaction volumes and additional dimensions.
• Maintainability: other developers and analysts can understand the model more easily.

1. Flat Tables, Star Schemas and Snowflake Schemas

1.1 Flat Tables

A flat table stores information from different business entities together in a single table. Instead of separate tables for customers, products, dates, and sales, all the relevant information is stored as columns in one table.

Structure of a flat table

Advantages and disadvantages of a flat table

Advantages	Disadvantages
Simple to understand and build	Repeats descriptive values on every row
No relationships to manage	Wide tables with many high-cardinality columns
Fast to prototype	Slower refresh and harder maintenance as data grows
Fine for small datasets	Hard to share dimensions across multiple fact tables

Appropriate use:

• Small, simple datasets
• One-off or ad hoc analysis
• Datasets with no repeating relationships
• Prototypes or personal dashboards
• Data that already arrives flat

1.2 Star Schema

A star schema is a modelling pattern that organizes data into one central fact table (the transactions: sales, orders, events) surrounded by several dimension tables (the descriptive attributes: who, what, where, when). It is the recommended pattern for Power BI models.

• Fact table (center): contains quantitative data.
• Dimension tables (points): contain descriptive attributes.

Structure of a star schema

Advantages and disadvantages of a star schema

Advantages	Disadvantages
Simple, intuitive structure	Requires upfront design effort
Fast, predictable queries	Needs data preparation to split source data into facts and dimensions
Easy to write DAX against	Some denormalization inside dimensions
Shared dimensions across reports	Not needed for tiny, one-off datasets

Appropriate use of a star schema:

1. BI analysts and report developers building Power BI, Tableau, or other dashboards on recurring, growing datasets.
2. Data warehouse and data engineering teams designing the backend structure that feeds reporting tools.
3. Organizations with multiple related datasets (sales, inventory, finance) that want shared, consistent dimensions across reports.

1.3 Snowflake Schema

A snowflake schema takes the star schema one step further. Instead of stopping at a single table per dimension, the dimensions themselves are normalized into sub-tables (for example, Product → Subcategory → Category).

Structure of a snowflake schema

Advantages and disadvantages of a snowflake schema

2. Fact Tables and Dimension Tables

• Fact table: contains quantitative, measurable data (for example, revenue and quantity sold) alongside foreign keys that link to the dimension tables.
• Dimension tables: contain descriptive attributes or context about the facts (for example, customer names, product categories, store locations).

Numeric columns vs. descriptive attributes

Numeric fact columns such as Quantity and Sales Amount are additive: they can be summed, averaged, or compared over time. In Power BI you aggregate them with DAX measures such as Total Sales = SUM(Sales[Sales Amount]).

Descriptive attributes (Customer Name, Product Name, Category, Location, Date) are not aggregated. They are used to filter, group, or label the numbers. This is why numeric values live in the fact table and descriptive attributes live in dimension tables.

Grain

Grain defines exactly what one row in the fact table represents. For example, "one row per order line" or "one row per product per store per day." Decide the grain first, because every column in the fact table must be true at that level of detail.

3. Relationships in Power BI

A relationship is established by matching the primary key in a dimension table to the corresponding foreign key in the fact table. Power BI stores data in separate tables, so without a relationship those tables have no way of knowing how they relate.

Key terms

• Cardinality: describes how rows in one table relate to rows in another, based on the count of matching rows.
• Primary key: a column that uniquely identifies each row in a table.
• Foreign key: a column that references the primary key of another table.

Relationship cardinalities

• One-to-many (1:*): a single row in the "one" table matches many rows in the "many" table. Example: one Customer → many Sales rows.
• One-to-one (1:1): each row in Table A links to exactly one row in Table B, and vice versa. Both sides need unique keys.
• Many-to-many (*:*): rows in Table A can match multiple rows in Table B and vice versa, because neither side has unique values in the key column.

4. Filter Direction

Single-direction filtering

Filters flow only from the "one" side of the relationship (usually the dimension table) to the "many" side (usually the fact table). Filters do not flow in the opposite direction.

Example: selecting a Category in a slicer filters the Sales table, but selecting a Sales row does not filter the Category table.

Both (bidirectional) filtering

The filter direction is set to Both, so filters travel from the dimension to the fact table and from the fact table back to the dimension.

Advantages

• More flexible filtering across related tables.
• Useful in some complex reporting scenarios.

Considerations

• Can create ambiguous filter paths.
• May cause unintended cross-filtering between tables.
• Can make the model more complex and hurt performance.
• For many-to-many scenarios, a bridge table with single-direction relationships is usually a safer solution than bidirectional filtering.

5. Joins in Power Query

A join combines two tables by matching values in key columns. In the Power Query interface this operation is called Merge Queries. The join type decides which rows are kept.

Inner Join

Keeps only records that have matching values in both tables.

Left Outer Join

Keeps all rows from the left (first) table and only the matching rows from the right (second) table. Unmatched left rows get nulls in the columns from the right table.

Right Outer Join

Keeps all rows from the right (second) table and only the matching rows from the left (first) table.

Full Outer Join

Keeps all records from both tables, whether or not a match exists.

Left Anti Join

Keeps only the rows from the left table that have no match in the right table.

Right Anti Join

Keeps only the rows from the right table that have no match in the left table.

Both Power Query joins and Power BI relationships connect data from multiple tables, but they serve different purposes and happen at different stages of the workflow.

6. Power Query Joins vs Power BI Relationships

What is a Power Query merge?

A merge combines data from two or more tables during the data preparation stage, before the data is loaded into the model. Power Query physically brings columns from one table into another based on matching values.

Sales table

SalesID	ProductID	Quantity
1001	P01	5
1002	P02	3

Products table

ProductID	ProductName
P01	Laptop
P02	Mouse

After a Left Outer Join:

SalesID	ProductID	Quantity	ProductName
1001	P01	5	Laptop
1002	P02	3	Mouse

Does a merge physically combine data? Yes. It copies columns from one table into another.

What is a Power BI relationship?

A relationship connects tables within the model without physically combining them. The tables remain separate, and Power BI uses the relationship to let filters and calculations flow between them during analysis.

Does creating a relationship combine tables? No. Relationships do not copy, append, or merge data. They only define how tables interact.

Where each one happens in the workflow

Data Sources
     ↓
Power Query        ← merges happen here
     ↓
Load Data
     ↓
Data Model         ← relationships are created here
     ↓
Reports

When to use a merge

• You need additional columns in a single table.
• You are cleansing or enriching data.
• You are working with small datasets.
• You are preparing staging tables before modelling.

When to use a relationship

• You are building a star schema.
• You are working with large datasets.
• You are creating scalable BI solutions.
• Multiple fact tables need to share common dimensions.

How excessive merging affects a data model

Excessive merging tends to produce one large flat table full of repeated values such as Customer Name, Product Name, Region, and Category. This can lead to:

• Increased model size and memory consumption
• Wider tables with many high-cardinality columns
• Slower refresh times
• Harder maintenance
• Ambiguity when several fact tables need the same dimensions

Why keep fact and dimension tables separate?

• Reduced redundancy: dimension information is stored once instead of thousands of times.
• Better performance: smaller dimension tables filter faster and keep the model lean.
• Easier DAX: measures work against clean fact columns, and slicers use clean dimension columns.
• Improved scalability: you can add new fact and dimension tables without redesigning the model.
• Better readability: developers can clearly see what is being measured (facts) and how it is categorized (dimensions).

Practical business example

Merge approach: a company merges Sales, Customers, Products, and Regions into a single table of one million rows. Every row repeats customer, product, and region details.

Result: larger model, more redundancy, lower performance.

Relationship approach: separate tables are kept and connected by relationships.

Fact Sales

Customer Key	Product Key	Region Key	Date Key	Sales Amount
1	10	100	20260101	250

Dim Customer

Customer Key	Customer Name
1	Amina Otieno

Dim Product

Product Key	Product Name
10	Laptop

Dim Region

Region Key	Region
100	Nairobi

Dim Date

Date Key	Date
20260101	2026-01-01

Result: smaller model, better performance, easier maintenance, greater scalability.

7. Recommended Power BI Model

For most business intelligence projects, I recommend a star schema with one-to-many relationships and single-direction filtering, based on performance, maintainability, scalability, and ease of report development.

Why not a flat table?

Flat tables are easy to understand, but they become hard to maintain as datasets grow:

• High data redundancy
• Larger model sizes
• Slower refresh
• Poor scalability
• Difficult maintenance

They suit small prototypes but are generally not ideal for enterprise BI solutions.

Why not a snowflake schema?

Snowflaking saves a little storage but adds more tables, more relationships, and longer filter paths. In Power BI, dimension tables are usually small, so the storage saving is minor. The cost is a model that is harder to navigate and slower to query. Unless a source system forces it, flatten sub-dimensions into a single dimension table (the Product table carries Subcategory and Category).

Best practices to go with the star schema

• Use a dedicated Date table and mark it as a date table.
• Hide foreign keys in the fact table so report authors use dimension columns.
• Use role-playing dimensions when one dimension plays several roles (for example, Order Date and Ship Date both link to Dim Date).
• Understand active vs inactive relationships: only one relationship between two tables can be active. Use USERELATIONSHIP() in DAX to activate an inactive one.
• Prefer single-direction filtering and avoid bidirectional filters unless truly necessary.

Conclusion and Key Takeaways

• Data modelling is as important as data transformation and visualization.
• Flat tables are fine for quick prototypes but do not scale.
• Star schemas are the recommended pattern for Power BI: fast, simple, and easy to maintain.
• Snowflake schemas add complexity that rarely pays off in Power BI.
• Use Power Query merges for data preparation and relationships for modelling.
• Keep facts and dimensions separate, define the grain, and use one-to-many relationships with single-direction filters.

A well-built model makes every report, measure, and refresh easier for you and for the next person who inherits it.