DEV Community: Ayam Abuga

Power BI Data Modelling Explained !

Ayam Abuga — Sun, 13 Sep 2026 12:44:14 +0000

Schemas, Relationships, Filter Direction and Joins

If your Power BI dashboard looks great but your numbers are wrong, the problem may not be your DAX. It may be your data model :)

When building a Power BI report, it is tempting to focus on charts, slicers and dashboards. But underneath every reliable Power BI report is a well-designed data model.

Your model determines how tables communicate, how filters travel, how DAX calculations behave, and how easily your report can scale as the amount of data grows.

In this article, we'll build a practical understanding of data modelling, fact and dimension tables, schemas, relationships, filter direction and Power Query joins.

Why Data Modelling Matters

Imagine a retail company with millions of sales transactions.

Every transaction contains information such as:

Customer
Product
Date
Location
Quantity
Sales amount

One option is to put everything into one enormous table.

Another option is to separate the data into logical tables and connect them.

That second approach is usually much better for business intelligence.

A good Power BI model provides:

Better performance when querying large datasets
Simpler DAX calculations
Clearer reports
Less data duplication
Predictable filtering
Better scalability
Easier maintenance

A useful way to think about modelling is:

The fact table tells you what happened. Dimension tables tell you who, what, when and where it happened.

1. Three Common Data Modelling Schemas

There are three structures you'll commonly encounter: flat tables, star schemas and snowflake schemas.

Flat Table

A flat table puts everything into one table.

┌──────────────────────────────────────────────┐
│                  SalesData                   │
├─────────┬──────────┬──────────┬─────────────┤
│ OrderID │ Customer │ Product  │ SalesAmount │
├─────────┼──────────┼──────────┼─────────────┤
│ 1001    │ John     │ Laptop   │ 80000       │
│ 1002    │ Mary     │ Mouse    │ 2000        │
│ 1003    │ John     │ Laptop   │ 80000       │
└─────────┴──────────┴──────────┴─────────────┘

The good

Flat tables are:

Easy to understand
Easy to import
Suitable for small datasets
Useful for simple reports

The problem

They create a lot of repetition.

If John makes 1,000 purchases, his name, location and other attributes may be repeated across 1,000 rows.

As the dataset grows, the table can become very wide and difficult to maintain.

Use a flat table when: the dataset is small and the analysis is simple.

2. Star Schema

The star schema is generally the preferred design for Power BI analytical models.

It contains a central fact table surrounded by dimension tables.

                    ┌──────────────┐
                    │   DimDate    │
                    └──────┬───────┘
                           │ 1
                           │
                           ▼ *
┌──────────────┐    ┌──────────────┐    ┌──────────────┐
│ DimCustomer  │1 ─►│  FactSales   │◄─ 1│  DimProduct  │
└──────────────┘    └──────┬───────┘    └──────────────┘
                           │ *
                           │
                           ▼ 1
                    ┌──────────────┐
                    │ DimLocation  │
                    └──────────────┘

The fact table contains measurable events.

The dimensions describe those events.

For example:

FactSales
-----------------
SalesID
DateKey
CustomerID
ProductID
LocationID
Quantity
SalesAmount
CostAmount

While:

DimProduct
-----------------
ProductID
ProductName
Category
Brand

Now we can write a simple measure:

Total Sales =
SUM(FactSales[SalesAmount])

Then DimProduct[Category] can filter the sales measure.

Why star schemas work so well

They provide:

Simple relationships
Good performance
Easier DAX
Low redundancy
Clear filter paths
Excellent scalability
Easier report development

For most Power BI projects, start with a star schema unless you have a specific reason not to.

3. Snowflake Schema

A snowflake schema takes the star schema and further separates dimensions.

For example:

DimCountry
     │
     ▼
DimRegion
     │
     ▼
DimCity
     │
     ▼
FactSales

Instead of storing:

DimLocation
-----------------
City
Region
Country

you might create separate tables.

Advantages

Snowflake schemas can:

Reduce redundancy
Represent complex hierarchies
Work well with highly structured source systems
Separate independently maintained entities

Disadvantages

The additional tables introduce more relationships.

That means:

More complex filtering
More complicated models
Potentially more difficult DAX
More effort for report developers

For a Power BI reporting model, I would generally favour a star schema over unnecessary snowflaking.

4. Fact Tables vs Dimension Tables

This distinction is fundamental.

Fact Tables

A fact table stores business events.

Examples:

FactSales
FactOrders
FactTransactions
FactInventory

A sales fact table might contain:

SalesID
DateKey
CustomerID
ProductID
Quantity
UnitPrice
Discount
SalesAmount

These numeric fields can be aggregated.

For example:

Total Quantity =
SUM(FactSales[Quantity])

Total Sales =
SUM(FactSales[SalesAmount])

Dimension Tables

Dimension tables contain descriptive attributes.

Examples:

DimCustomer
-----------------
CustomerID
CustomerName
Segment
City

DimProduct
-----------------
ProductID
ProductName
Category
Brand

DimDate
-----------------
DateKey
Date
Month
Quarter
Year

DimLocation
-----------------
LocationID
City
Region
Country

The simplest mental model is:

Type	Answers
Fact	What happened?
Customer dimension	Who?
Product dimension	What?
Date dimension	When?
Location dimension	Where?

5. Don't Ignore Fact Table Grain

One of the easiest ways to create incorrect Power BI calculations is to misunderstand grain.

Grain means:

What exactly does one row represent?

Suppose an order contains three products.

Order 1001
├── Laptop
├── Mouse
└── Keyboard

If the fact table grain is one row per order line, the data could look like:

OrderID   Product    Quantity
1001      Laptop     1
1001      Mouse      2
1001      Keyboard   1

The order appears three times.

That's correct because the grain is the order line.

If you incorrectly assume the grain is "one row per order", you may accidentally double-count revenue.

Always define the grain before building your relationships and measures.

6. Relationships in Power BI

Relationships tell Power BI how tables are connected.

The most common relationship is:

DimCustomer  1 ───────── * FactSales

One customer can have many sales.

For example:

DimCustomer

CustomerID
C001
C002
C003

But FactSales might contain:

SalesID   CustomerID
1001      C001
1002      C001
1003      C002
1004      C001

C001 is unique in the dimension but appears multiple times in the fact table.

That's exactly what we want.

7. Relationship Cardinality

One-to-Many (1:*)

The standard star-schema relationship.

DimProduct  1 ───────── * FactSales

One product can appear in many sales transactions.

Use this frequently.

One-to-One (1:1)

Each record on one side corresponds to one record on the other.

CustomerBasic  1 ───── 1 CustomerContact

This can be useful when logically separate information is maintained in separate tables.

However, don't create a 1:1 relationship simply because you can. If two tables can logically be one table, combining them may be simpler.

Many-to-Many (:)

Multiple records on both sides can match.

Student  * ───────── * Course

A student can take multiple courses, while a course can contain multiple students.

In BI models, many-to-many relationships require caution because they can create ambiguous filter paths.

A bridge table is often safer:

DimStudent  1 ── * Enrollment * ── 1 DimCourse

8. Primary Keys and Foreign Keys

A primary key uniquely identifies a row.

DimCustomer

CustomerID
----------
C001
C002
C003

A foreign key references that identifier.

FactSales

CustomerID
----------
C001
C001
C002
C001

This is why the relationship is:

CustomerID
   1
   │
   │
   *
CustomerID

The dimension must normally contain unique keys.

The fact table can contain repeated foreign keys.

If FactSales contains C999, but C999 doesn't exist in DimCustomer, you have a referential-integrity problem.

9. Active vs Inactive Relationships

Sometimes a fact table contains multiple dates:

FactSales

OrderDate
ShipDate
DeliveryDate

A Date dimension could have:

DimDate ─── Active ─── FactSales[OrderDate]

DimDate ─── Inactive ─ FactSales[ShipDate]

An inactive relationship isn't useless. It can be activated inside a DAX calculation:

Sales by Ship Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(
        DimDate[Date],
        FactSales[ShipDate]
    )
)

This allows one Date dimension to support multiple date roles.

10. Filter Direction

Relationships also control how filters move.

The normal star-schema pattern is:

DimProduct
     │
     │ Filter
     ▼
FactSales

Suppose the user selects:

Category = Electronics

Power BI filters DimProduct, which then filters the matching rows in FactSales.

The sales measure automatically responds:

Total Sales =
SUM(FactSales[SalesAmount])

Single Direction

Dimension ─────────► Fact

This should normally be your default.

Bidirectional

Dimension ◄────────► Fact

Filters can move in both directions.

Bidirectional filtering can be useful in specific scenarios, but overusing it can cause:

Ambiguous filter paths
Unexpected results
Hard-to-debug calculations
More complex models

A good rule is:

Use single-direction filtering by default. Add bidirectional filtering only when you can explain exactly why you need it.

11. Joins in Power Query

Relationships aren't the only way Power BI works with multiple tables.

Power Query provides Merge Queries, which performs joins during the data transformation stage.

Consider:

Customers

CustomerID   Name
C001         John
C002         Mary
C003         Peter
C004         Jane

Orders

OrderID   CustomerID
1001      C001
1002      C001
1003      C002
1004      C005

We can join them using CustomerID.

Left Outer Join

Keeps all records from the left table and matching records from the right.

C001 → Order
C002 → Order
C003 → No Order
C004 → No Order

Useful for finding all customers and their orders, including customers who have never ordered.

Right Outer Join

Keeps all records from the right table and matching records from the left.

C001 → John
C002 → Mary
C005 → No Customer

Useful when all records from the right-hand table must be retained.

Full Outer Join

Keeps everything from both tables.

C001 → John
C002 → Mary
C003 → Peter
C004 → Jane
C005 → No Customer

Useful for data reconciliation and identifying unmatched records.

Inner Join

Keeps only matching records.

C001 → John
C002 → Mary

Useful when you only want records that exist in both datasets.

Left Anti Join

Returns records from the left table that don't exist in the right.

C003 → Peter
C004 → Jane

This answers:

Which customers don't have orders?

Right Anti Join

Returns records from the right table that don't exist in the left.

C005 → Order 1004

This answers:

Which orders belong to customers missing from the customer table?

Join Cheat Sheet

Join	Keeps
Left Outer	Everything on left + matches
Right Outer	Everything on right + matches
Full Outer	Everything
Inner	Matches only
Left Anti	Left-side unmatched
Right Anti	Right-side unmatched

12. Merge vs Relationship: Don't Confuse Them

This is one of the most important Power BI concepts.

Power Query Merge

A merge physically combines data during transformation.

Customers + CustomerDetails
          ↓
    Combined Query

Columns from one table can become part of the resulting query.

Power BI Relationship

A relationship keeps tables separate.

DimCustomer  1 ───────── * FactSales

Nothing is physically copied into the other table.

Instead, Power BI uses the relationship to propagate filters.

Think of it this way:

Merge = combine the data.

Relationship = connect the data.

13. When Should You Merge?

Use a merge when two datasets logically belong together or when transformation requirements make it appropriate.

However, don't automatically merge every table.

For example, putting:

CustomerName
CustomerCity
CustomerSegment
ProductName
ProductCategory
Brand
Country
Region

into every sales row creates a very wide fact table.

Instead:

DimCustomer ──► FactSales ◄── DimProduct
                         ▲
                         │
                      DimDate

keeps descriptive information separate.

This reduces redundancy and produces a cleaner analytical model.

14. The Model I Would Build

For a typical retail BI project, my model would look like this:

                    ┌─────────────┐
                    │   DimDate   │
                    └──────┬──────┘
                           │ 1
                           ▼ *
┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│ DimCustomer  │1 ──►│  FactSales   │◄── 1│  DimProduct  │
└──────────────┘     └──────┬───────┘     └──────────────┘
                            │ *
                            ▼ 1
                     ┌─────────────┐
                     │ DimLocation │
                     └─────────────┘

I would use:

Star schema
1-to-many relationships
Unique dimension keys
Repeating foreign keys in fact tables
Single-direction filtering
Clearly defined fact grain
Measures for business calculations
Inactive relationships only where necessary

15. Why Star Schema Wins for Most Power BI Projects

A star schema provides the best balance between simplicity and analytical power.

Performance

The model avoids unnecessary duplication and provides straightforward relationships.

DAX

Measures are easier to write because dimensions provide filtering context.

Readability

Developers can immediately identify facts and dimensions.

Scalability

New dimensions and business processes can be added without redesigning everything.

Maintainability

The model has clear responsibilities:

FACTS       → Business events
DIMENSIONS  → Descriptive context
RELATIONSHIPS → Filter propagation
MEASURES    → Business calculations

A snowflake schema still has its place, particularly for highly complex dimensions. A flat table can also be perfectly reasonable for small datasets.

But when building a serious Power BI analytical solution, star schema should usually be your first choice.

Final Takeaway

Power BI modelling isn't simply about connecting tables until the visualisations work.

It's about designing a structure that represents how the business thinks about its data.

Remember these five rules:

1. Define the grain of your fact table.

2. Keep measurable events in fact tables.

3. Keep descriptive attributes in dimensions.

4. Prefer 1-to-many, single-direction relationships.

5. Use Power Query merges when you need to combine data, and relationships when you need to connect data.

If you get the model right, everything built on top of it becomes easier—DAX, dashboards, filtering, performance and future development.

And that's the real power of Power BI:

A great dashboard starts with a great data model.

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Ayam Abuga — Sat, 29 Aug 2026 23:10:19 +0000

Introduction

Data analytics does not always begin with complicated programming or advanced statistical tools. For beginners, it can start with something as familiar as Microsoft Excel.

During Week 1 of my data analytics learning, I focused on the fundamentals of preparing data for analysis. These included formatting, sorting, filtering, identifying missing values, removing duplicates, correcting inconsistent data and validating information.

To apply these concepts practically, I worked with an HR dataset containing employee information such as Employee ID, names, departments, salaries, hire dates, ages, performance scores, employee types, office locations and other employee-related information.

The dataset contained 877 records and 21 columns. Although it looked like a normal spreadsheet at first, closer inspection revealed several data-quality problems. This made it a useful dataset for practicing the data-cleaning techniques covered during Week 1.

Inspecting the Raw Dataset

The first step in cleaning data is to understand what you have before changing anything.

I opened the HR dataset in Excel and reviewed the columns and records. Some of the main fields included:

Employee ID
First Name
Last Name
Department
Salary
Hire Date
Age
Gender
Performance Score
Employee Type
Office Location
Project Count
Remote Work Status
Annual Training Hours
Manager Feedback Score

Each row represented an employee, while each column represented a specific attribute.

Raw Dataset

Before cleaning, the dataset was as follows;

The initial inspection revealed missing values, duplicate employee IDs, inconsistent spelling, invalid dates and incorrect data types.

This showed me why inspecting data before analysis is important. Data can look organized while still containing errors.

Formatting the Data

After inspecting the dataset, I formatted the worksheet to make it easier to work with.
I confirmed that the data contained headers.
Using a table made it easier to work with the 877 records because Excel automatically provided filtering options for each column, as seen below;

I also adjusted column widths and made sure that the headings and values were easy to read. This is done by selecting the whole dataset, and autofitting the column width, i.e;

Formatting may not change the actual data, but it improves readability and makes the cleaning process much easier.

For example, salary values can be displayed consistently with commas and in same format, as numbers and not texts, while dates can be displayed using one standard format,i.e;

Identifying and Removing Duplicates

Duplicate records were another important issue in the dataset.

Employee ID is particularly useful when checking for duplicates because it is intended to identify an employee.

I found employee IDs that appeared more than once in the dataset.

If the same employee record is accidentally entered twice, keeping both records could produce incorrect results. For example, the number of employees could be overstated.

Excel provides a Remove Duplicates feature under the Data tab.
The process is:

Select the dataset.
Click Data.
Select Remove Duplicates.
Select the column used to identify duplicates.
Review the records.
Remove confirmed duplicates. This is illustrated below;

However, duplicate removal should be done carefully.

A repeated ID does not automatically prove that the entire record is incorrect.

The records should first be compared to determine whether they are genuine duplicates.

This taught me an important lesson: data should be investigated before it is deleted.

Correcting Inconsistent Data

Another problem I discovered was inconsistent text.

For example, the Department column contained variations such as:

Marketing
Markting

There were also different versions of the IT department:

IT
I.T
Info Tech
Information Tech

The Office Location column had similar problems:

London
Londn

and:

San Francisco
San Fransisco
SF

Inconsistent Values

These differences can cause problems when sorting, filtering or eventually grouping the data.

For example, Excel would treat London and Londn as different values even though they may represent the same location.

Some formatting inconsistencies can be handled with Excel functions such as:

=TRIM(A2)

which removes unnecessary spaces.

Capitalization can also be standardized using:

=PROPER(A2)

However, spelling mistakes such as Londn or Markting need to be corrected systemically based on the expected value.

This showed me that data cleaning is not simply about formatting. It also requires understanding what the values are supposed to represent.

Validating the Data

After cleaning obvious problems, the next step was validation.

Validation means checking whether the values make sense for their particular columns.

For example, the Age column should contain reasonable numerical values.

However, the dataset contained a value such as:

thirty

while most other ages were numerical.

This value needs to be investigated because it does not follow the expected format.

The Hire Date column also contained invalid dates, including:

2019-02-30

February does not have 30 days, so this cannot be a valid calendar date.

The Performance Score column also contained unusual values, including text and values outside the expected range.

These examples demonstrated that validation requires more than checking whether a cell contains data.
Validation is usually done before data is entered, to standardize the texts entered in the dataset, i.e,

The question should be:

Does this value make sense for this particular field?

Basic Sorting

Sorting was another technique I used when working with the dataset.

Excel allows data to be arranged from smallest to largest, largest to smallest or alphabetically.

For example, I could sort the Salary column from largest to smallest to identify the highest salaries.

I could also sort Employee IDs to make repeated IDs easier to identify.

Sorting the Department or Office Location columns can also make spelling inconsistencies easier to notice because similar values appear together.

Sorting therefore serves both an organizational and data-cleaning purpose.

Basic Filtering

Filtering allows me to temporarily display only the records I need.

For example, I could filter the Department column to show only employees working in HR.

I could also filter the Age column or Salary column to investigate specific ranges.

Most importantly for data cleaning, I could filter for blank values to quickly find missing information.

With 877 records, this is much faster than manually checking every row.

Sorting and filtering are simple Excel features, but they are extremely useful when working with real datasets.

My Data-Cleaning Workflow

After working with the dataset, I developed a simple workflow:

Raw Dataset
     ↓
Inspect
     ↓
Format
     ↓
Find Missing Values
     ↓
Identify Duplicates
     ↓
Remove Confirmed Duplicates
     ↓
Correct Inconsistent Values
     ↓
Validate Data
     ↓
Sort and Filter
     ↓
Clean Dataset

The final cleaned file looked as below;

The purpose of this process is to make the dataset more consistent and reliable before using it for further analysis.

What I Learned

Working with the HR dataset helped me understand that data cleaning is an essential part of data analytics.

The dataset contained problems that would have been easy to overlook without careful inspection. These included missing values, duplicate Employee IDs, inconsistent department names, misspelled locations, invalid dates and incorrect data types.

The Excel tools I learned during Week 1 provided practical ways to deal with these problems.

Formatting made the dataset easier to read. Filtering helped locate missing values. Sorting helped identify patterns and inconsistencies. Remove Duplicates helped eliminate confirmed duplicate records. Validation helped determine whether values were reasonable and correctly formatted.

Most importantly, I learned that cleaning data requires judgment. An unusual value should not immediately be deleted. It should first be investigated and understood.

Conclusion

Excel provides a simple but powerful starting point for learning data analytics.

Working with the HR dataset allowed me to apply the Week 1 concepts to a realistic data-cleaning problem rather than simply learning them theoretically.

The process can be summarized as:

Inspect → Format → Clean → Validate → Sort/Filter → Prepare

The biggest lesson I took from this exercise is that analysis should not begin with assumptions about the data. It should begin by understanding and checking the data.

A clean dataset produces more reliable results, while dirty or inconsistent data can lead to misleading conclusions.

As I continue learning data analytics, these basic Excel skills will provide a foundation for working with larger datasets and more advanced tools.

Before analyzing data, make sure the data is clean. Before trusting the data, validate it.