Building Better Power BI Models; A Guide to Relationships, Fact Tables, Star Schema, and Joins

Mugendi Mung'athia — Wed, 01 Jul 2026 20:48:05 +0000

Introduction

While working with Power BI, I thought building reports was mainly about creating attractive charts and dashboards. As I worked with datasets containing sales, customers, products, and dates, I discovered that the real foundation of every Power BI report is the data model. Without properly defined relationships, visuals cannot communicate with each other effectively, calculations become unreliable, and filtering data becomes frustrating.

This is where concepts such as fact tables, dimension tables, relationships, star schema, snowflake schema, cardinality, and cross-filter direction become essential. These are the building blocks that allow Power BI to transform raw data into meaningful insights.

I'll explore how these concepts work together in Power BI, why choosing the right schema matters, and how good data modeling can improve both report performance and the overall reporting experience.

Data Modeling in Power BI

Data modeling is the process of organizing data into related tables so that Power BI can analyze and visualize it correctly. Instead of storing everything in one large table, data is separated into logical tables that work together through relationships.

A well-designed data model improves report performance, makes DAX calculations more accurate, and keeps dashboards easy to maintain as your data grows.

In Power BI, data modeling takes place in the Model View, where you create and manage relationships between tables.

Fact Tables

A Fact Table is the central table in a Power BI data model. It stores measurable business data such as sales, revenue, profit, quantity sold, or transactions. Unlike dimension tables, fact tables mainly contain numbers that can be aggregated and analyzed.

Fact tables usually grow very large because every business transaction is recorded here. They also contain foreign keys that link to dimension tables such as Products, Customers, or Dates.

Example of a Fact Table

SaleID	ProductID	CustomerID	DateID	Quantity	SalesAmount
1001	P001	C001	D001	2	1500
1002	P003	C002	D001	1	800

Dimension Tables

A Dimension Table stores descriptive information about the data in a fact table. Instead of containing business transactions, it provides context that helps us understand and analyze those transactions.

For example, instead of storing product names in the Fact Sales table, Power BI links to a Product dimension table using a common key such as ProductID.

Example of a Dimension Table

ProductID	ProductName	Category	Brand
P001	Laptop	Electronics	HP
P002	Phone	Electronics	Samsung

Why Dimension Tables Matter

Dimension tables make reports easier to read by replacing IDs with meaningful information such as product names, customer names, locations, and dates.

Schemas

A schema is the way data is organized and structured in a database or data model. In Power BI, a schema defines how fact tables and dimension tables are arranged and connected through relationships.

Choosing the right schema is important because it affects report performance, data organization, and how easy it is to build dashboards and write DAX measures.

Power BI primarily uses two schema designs:

Star Schema
Snowflake Schema

Both schemas organize data differently, and each has its own strengths and weaknesses. Understanding when to use each one is an important part of building an efficient Power BI data model.

1. Star Schema

A Star Schema is the most recommended data model in Power BI. It consists of one central fact table connected directly to multiple dimension tables. Since the structure resembles a star, it is called a Star Schema.

                 Dim Date
                     |
                     |
Dim Customer ---- Fact Sales ---- Dim Product
                     |
                     |
               Dim Store

Advantages

Faster query performance.
Simple relationship structure.
Easy to troubleshoot.
It is easy to understand.
Improves report performance.
Simplifies DAX calculations.
Makes reports easier to maintain.

Disadvantages

Dimension tables may contain duplicated information.
Can use slightly more storage than a normalized model.

2. Snowflake Schema

A snowflake schema is an extension of the Star Schema where dimension tables are further divided into smaller related tables. This process is known as normalization, and it helps reduce data redundancy.

               Dim Category
                     |
               Dim Product
                     |
                     |
Dim Customer ---- Fact Sales ---- Dim Date

In this model, the Product table no longer stores category information directly. Instead, it connects to a separate Category table.

Advantages

Less data redundancy.
Better data integrity.
Easier to update shared information.
Reduces duplicated data.
Improves data consistency.
Suitable for complex datasets.

Disadvantages

More relationships to manage.
Slightly slower queries due to additional joins.
More complex to understand than a Star Schema.

Cardinality in Power BI

Cardinality defines how rows in one table relate to rows in another. Choosing the correct cardinality ensures that relationships work as expected and that reports return accurate results.

Power BI supports four types of cardinality.

1. One-to-One

Each row in one table matches exactly one row in another table.

Example: An employee table linked to an employee details table, where each employee has only one record.

2. One-to-Many

This is the most common relationship in Power BI. One record in a dimension table relates to many records in a fact table.

Example: One product can appear in many sales transactions.

This is the recommended cardinality when building a Star Schema.

3. Many-to-One

This is simply the reverse of a One-to-Many relationship. Many records in one table relate to a single record in another table.

Example: Many sales records belonging to one customer.

4. Many-to-Many

Records in both tables can have multiple matching values.

Example: Students enrolling in multiple courses, while each course has multiple students.

Cross Filter Direction

Cross Filter Direction determines how filters move between related tables in Power BI. It controls how one table influences another when interacting with visuals and calculations.

Power BI provides two cross filter options:

1. Single

A single cross filter allows data to flow in one direction, usually from a dimension table to a fact table.

Dim Product  ─────────▶  Fact Sales

This is the default option in Power BI and is recommended for most data models because it improves performance and reduces ambiguity.

2. Both

A both cross filter allows data to flow in both directions.

Dim Product  ◀────────▶  Fact Sales

This option is useful when two tables need to filter each other. However, it should be used carefully because it can create ambiguous relationships and slow down report performance.

Joins

Before creating relationships in the data model, Power BI allows us to combine data using Merge Queries in Power Query. A merge operation joins two tables based on a matching column, similar to how relationships work in databases.

To create a merge, go to:

Home → Merge Queries

Power BI provides several join types depending on the results you want.

1. Inner Join

Returns only matching rows from both tables.

Customers          Orders

CustomerID         CustomerID
1                  1
2                  2
3                  4

Result:
1
2

Used when : you only need records that exist in both tables.

2. Left Outer Join

Returns all rows from the first table and matching rows from the second table.

Customers          Orders

1                  1
2                  2
3                  4

Result:
1
2
3

Used when : the first table is your primary table and you don't want to lose any records.

3. Right Outer Join

Returns all rows from the second table and matching rows from the first table.

Used when : the second table is the primary source of information.

4. Full Outer Join

Returns all rows from both tables, whether they match or not.

Use when: you want a complete view of both datasets.

5. Left Anti Join

Returns only rows from the first table that do not have a match in the second table.

Used when: Finding missing records.

6. Right Anti Join

Returns only rows from the second table that do not have a match in the first table.

Used when: Identifying records that exist in one dataset but not the other.

Importance of Joins

Joins help prepare data before it enters the Power BI model. They are useful for:

Combining datasets.
Identifying missing records.
Enriching data with additional information.
Cleaning and transforming data before analysis.

Conclusion

Data modeling is the foundation of every successful Power BI report. Understanding concepts such as relationships, fact tables, dimension tables, schemas, cardinality, cross-filter direction, and Power Query joins makes it easier to build reports that are both accurate and efficient.

As I continue learning Power BI, I've realized that investing time in building a strong data model before creating visuals leads to cleaner dashboards, better performance, and more meaningful insights.

How Excel is Used in Real-World Data Analysis

Mugendi Mung'athia — Sun, 07 Jun 2026 19:32:09 +0000

Introduction

Most people I included mainly associate Microsoft Excel with storing information, data entry and performing basic calculations. However, after enrolling in Data Science and Analytic course and spending my first week learning Excel, I have come to appreciate how important it is in handling and analyzing data. I now understand why Excel continues to be used by businesses, researchers, and organizations around the world. It provides simple but powerful tools that help users organize data, identify patterns, and make informed decisions. Excel isn't just a basic spreadsheet program, it is essentially the backbone of real world data analysis and the quiet engine behind decisions making in many organizations.

Ways Excel is used in real-world data analysis

1. Business Decision-Making

One of the most common uses of Excel in the real world is supporting business decisions. Companies collect large amounts of information such as sales records, customer data, and inventory levels. Excel helps organize this information into a format that is easy to understand and analyze.

For example, a business can use Excel to compare monthly sales and identify which products are performing best. Managers can then use this information to make decisions about stock levels, promotions, and future business strategies.

2. Financial Reporting

Excel is also widely used in financial management and reporting. Organizations use spreadsheets to track expenses, prepare budgets, calculate profits, and monitor overall financial performance.

A company can use Excel to compare actual spending against planned budgets and identify areas where costs are increasing. This makes it easier to manage resources and make financial plans based on accurate information.

3. Marketing Performance Analysis

Marketing teams often use Excel to measure the success of their campaigns. Information such as website visits, social media engagement, customer responses, and sales can be recorded and analyzed in spreadsheets.

By studying this data, businesses can determine which marketing strategies are effective and which areas need improvement. This helps organizations make better decisions about future marketing activities.

Excel Features That I have learned

1. SUM Function

The SUM function is used to add. It can be applied when calculating total sales, expenses, or quantities in a dataset. Instead of adding numbers manually, Excel performs the calculation automatically and accurately. Here is an example:

2.AVERAGE Function

The AVERAGE function calculates the mean value of a group of numbers. This can be useful when analyzing average sales, average test scores, or average monthly expenses. It helps provide a general understanding of the data. Here is an example:

3.COUNT IF Function

The COUNT IF function is used to determine how many cells contain numerical values. This is especially useful when working with large datasets because it helps users know how many records are available for analysis. Here is an example:

My personal reflection:

Learning Excel has changed the way I view data. Previously, I saw data as just numbers arranged in rows and columns. Now I understand that data can provide valuable insights when it is organized and analyzed properly.

What has impressed me most is how even simple Excel formulas can help answer important questions and reveal patterns that might not be obvious at first glance. I have also realized that data analysis is not only about working with numbers but also about finding information that can support decision-making.

As I continue with my Data Science and Analytics course, I am excited to learn more tools that supplement Excel and discover how they can be used to solve real-world problems.

DEV Community: Mugendi Mung'athia

Building Better Power BI Models; A Guide to Relationships, Fact Tables, Star Schema, and Joins

Introduction

Data Modeling in Power BI

Fact Tables

Example of a Fact Table

Dimension Tables

Example of a Dimension Table

Why Dimension Tables Matter

Schemas

1. Star Schema

Advantages

Disadvantages

2. Snowflake Schema

Advantages

Disadvantages

Cardinality in Power BI

1. One-to-One

2. One-to-Many

3. Many-to-One

4. Many-to-Many

Cross Filter Direction

1. Single

2. Both

Joins

1. Inner Join

2. Left Outer Join

3. Right Outer Join

4. Full Outer Join

5. Left Anti Join

6. Right Anti Join

Importance of Joins

Conclusion

How Excel is Used in Real-World Data Analysis

Introduction

Ways Excel is used in real-world data analysis

1. Business Decision-Making

2. Financial Reporting

3. Marketing Performance Analysis

Excel Features That I have learned

1. SUM Function

2.AVERAGE Function

3.COUNT IF Function

My personal reflection: