UNDERSTANDING DATA MODELLING IN POWER BI

In today’s data-driven environments, tools like Power BI make it easy to build reports, but getting them to work correctly is where the real challenge lies. This article takes a practical look at how to structure your data effectively so your reports remain accurate, intuitive, and easy to maintain.

DATA MODELLING

What is Data Modelling?
Data modeling is the art of structuring, defining, and connecting your data tables so that Power BI can answer analytical questions correctly. It helps you decide: How many tables you need, what goes in each table, and how those tables connect.

HOW TO PERFORM JOINS IN POWER QUERY
A join is a method of linking tables using a shared field so that related data can be viewed or analyzed as a single dataset. In Power BI, joins are called Merge Queries.

Heres a simple example
Table 1

Table 2

Steps

1. Load both tables into Power Query
2. Click on the Customers table
3. Go to Home → Merge Queries
4. Select:
5. First table: Customers
6. Second table: Orders
7. Click the matching column in both tables:
8. Customer ID → Customer ID
9. Choose join type (start with Left Outer)
10. Click OK

Final result

TYPES OF JOINS

Left Join
Returns all rows from the left table (Customers) and matches from the right (Orders). If there’s no match → NULL.

Illustration

All customers are included, even if they have no orders.

Right Join
Keeps all rows from the right table (Orders) and adds matching customers; unmatched rows display NULL.

All orders are included, even if no matching customer exists.

Inner Join

Returns only rows that exist in both tables (matching keys).

Samuel (No orders) and Order 300 (No customer) are excluded.

Full Outer Join

Includes all rows from both tables, matching where possible and filling unmatched fields with NULL.

Combines the effect of LEFT + RIGHT joins.

POWER BI RELATIONSHIPS

In Power BI, a relationship defines how tables are connected via one or more columns. Relationships enable data from different tables to interact seamlessly in visuals, filters, and calculations.

TYPES OF RELATIONSHIPS

One-to-Many (1:M)

One row in the first table matches many rows in the second table.
Example:
Table: Customers (1 row per customer)
Table: Orders (multiple orders per customer)

Many-to-Many (M:M)

Rows in both tables can have multiple matches in the other table.
Example: A student can enroll in many courses, and each course can have many students

One-to-One (1:1)

One row in the first table matches exactly one row in the second table.
Example:
Table: Employee
Table: Employee Details (with their address, department, etc.)

Active vs. Inactive Relationships

Active Relationship:
The default relationship used for calculations and filtering.
Only one active relationship is allowed between two tables.

Inactive Relationship:
Exists but is not used by default in visuals.
Can be activated in DAX formulas using the USERELATIONSHIP()
function

JOINS VS RELATIONSHIPS

Illustration:

CROSS FILTER FUNCTION

Single Direction:

Filters flow one way, from the (1 side) to the (M side only).

Both Direction:
Filters flow in both directions, useful for complex relationships or M:M scenarios.
Use carefully as it can create circular filter paths and slow queries.

Cardinality
It describes how many rows in one table relate to rows in another table. Determines how filters propagate and how aggregations behave.
Types: 1:1, 1:M, M:1, M:M

FACT VS DIMENSION TABLES

Fact Tables
Stores measurable, transactional data and contains numeric metrics (like sales amount, quantity) and foreign keys referencing dimensions.

Dimension Tables
Store descriptive attributes about entities, showing the "who, what, where" behind each transaction.

TYPES OF SCHEMAS

Star Schema

This is a data model where: A central fact table (e.g., Orders) is directly connected to dimension tables with combined attributes.
Each dimension is independent (not split further).

Illustration

Snowflake Schemas
This is an extension of the star schema where dimension tables are normalized (split into related sub-tables).

Illustration

COMMON MODELING ISSUES

1. Overusing Flat Tables - Leads to large data size, difficult updates, and inaccurate counts due to repeated data.
2. Granularity Mismatch - Occurs when fact and dimension tables are at different levels (e.g., order lines vs full orders), causing duplication.
3. Slicers Not Filtering -A slicer may appear but not affect results if the filter path is broken.This is usually caused by missing relationships or wrong direction.
4. Circular Relationships- Happens when tables filter each other in multiple paths, creating ambiguity. Leads to errors or incorrect calculations.
5. Missing Date Table- Without a proper date table, time-based calculations won’t work correctly.

CONCLUSION

Good Power BI modeling comes down to one core principle: keep facts and dimensions separate, connected by a clean star schema. This gives you fast queries, accurate numbers, and reports that scale. The discipline is simple: clean relationships, consistent granularity, sound dimensional design. Everything else follows.