MASTERING SCHEMAS, RELATIONSHIP AND DATA MODELLING IN POWER BI

PRUDENCE JEMUTAI — Mon, 06 Jul 2026 14:07:07 +0000

In data analytics, visuals are the face — but the model is the skeleton. Without a strong structure, even the most beautiful dashboard collapses under the weight of bad data. Power BI’s schemas and relationships are not just technical features; they are the architecture of truth in your reports. Let’s uncover how to design them for accuracy, speed, and clarity.

DATA MODELLING IN POWER BI

Data modelling is the process of organizing data into a structured format by creating tables, defining relationships, and establishing rules that allow Power BI to understand how the data is connected.
The goal of data modelling is to transform raw data into a format that is easy to analyse and visualize.

For example, a retail company may have separate datasets for customers, products, sales, employees, and dates. Instead of keeping these datasets isolated, Power BI connects them through relationships, allowing users to answer questions such as:

Which products generate the highest revenue?
Which customers purchase the most?
Which regions have the highest sales?
How have sales changed over time?

Understanding Schemas in Power BI
If you get the schema right, your DAX formulas will be simple and your reports will load instantly. If you get it wrong, your calculations will show incorrect totals and your visuals will lag.
Fact Tables (The "What Happened"): These store numeric metrics, events, or transactions. They are long, narrow, and constantly growing.
Examples: Sales transactions, store id, Product id, Sales id
Columns: Quantities, amounts, dates, and ID keys.

Dimension Tables (The "Context"): These store descriptive attributes about the facts. They are wide and contain mostly text.
Examples: Customers, products, employees, stores, dates.
Columns: Names, categories, regions, colours, addresses.

The Star Schema
In a Star Schema, your central Fact table is directly surrounded by your Dimension tables. When you look at the relationship diagram, it resembles a star.
How it works: Each dimension connects directly to the fact table using a Many-to-One (*:1) relationship.
Why it wins: Power BI is explicitly built and optimized for this layout. It requires the fewest database joins, which maximizes report speed.

The Snowflake Schema
How it works: A Sales fact table connects to a Product dimension, which then connects to a separate Product Category dimension.
Why to avoid it: While it saves a tiny amount of database storage, it forces Power BI to filter through multiple layers of tables to answer a single question. This severely degrades performance.

The Anatomy of a Relationship

When you draw a connection between two tables in Power BI's Model View, you are linking a column from Table A to a column in Table B. For this link to work, both columns must contain the exact same type of data (e.g., matching Product IDs, Customer Keys, or Dates).Every relationship is defined by three critical settings: Cardinality, Cross-Filter Direction, and State.
Cardinality: Defining the Data Matching Style
Cardinality tells Power BI how rows in one table match rows in another. There are four types:
Many-to-One (*:1): This is the industry standard. The "One" side is your dimension table containing a list of completely unique items (like a Master Product List). The "Many" side is your transactional fact table where those items appear multiple times (like a Sales History ledger).
One-to-One (1:1) [6]: Rare. Both tables share a unique list of matching keys. This is usually only used to split a single, massively wide table into two smaller ones for security or organizational reasons.
Many-to-Many (:) [6]: Highly dangerous. This occurs when neither table contains a unique list of keys (e.g., mapping a list of student classes to a list of teachers, where students have multiple teachers and teachers have multiple students). It introduces ambiguity and should be resolved using a Bridge Table instead.
Cross-Filter Direction: Controlling the Traffic Flow
Cross-filter direction dictates which way the filtering power flows across your relationship.
Single Direction (Standard) [6]: The absolute default and safest setting. Filters only flow from the One (1) side down to the Many () side. Clicking a specific customer filters the sales table; clicking a row in the sales table does not filter your customer master list.
**Both Directions (Bi-directional) [6]*: Allows filtering to flow both ways. While tempting, avoid this whenever possible. It drastically slows down report refresh times, creates confusing circular filter loops, and often causes your metrics to calculate incorrect numbers.

Relationship State: Active vs. Inactive

Active Relationship: Indicated by a solid line in the Model View. This is the default path Power BI uses to filter your data and calculate your regular DAX measures automatically.

Inactive Relationship: Indicated by a dashed or dotted line. This happens when you have multiple date columns in a fact table (e.g., a sales record has an OrderDate, ShipDate, and DeliveryDate) but can only link them all to a single Calendar table. You can leave the secondary dates inactive and turn them on temporarily using the USERELATIONSHIP() DAX function inside a measure.

Understanding Joins in Power BI

PRUDENCE JEMUTAI — Wed, 24 Jun 2026 13:25:36 +0000

Join is combining two tables based on matching column. In Power BI joins are done using merge queries.

The most common join types in Power BI are Inner Join,
Left Outer Join, Right Outer Join, Full Outer Join, Left Anti Join, and Right Anti Join.

An inner join returns only the records that have matching values in both tables. It is useful when you only want data that exists in both datasets.

An Outer Join returns records even when there is no matching value in the other table. Unlike an Inner Join, which only keeps matching records, an Outer Join preserves unmatched records and fills missing values with nulls.

Outer Join = Keep unmatched records.
Inner Join = Keep only matching records.

There are three types of Outer Joins

1 Left outer join – Returns all records from the left table and matching records from the right table.
2 Right Outer Join – Returns all records from the right table and matching records from the left table.
3 Full Outer Join – Returns all records from both tables, whether they match or not.

A Left outer join returns all records from the left table and only the matching records from the right table. If no match exists, the right-side columns contain null values. This join is commonly used when the left table contains the primary data that must be preserved.

A Right outer join works similarly but keeps all records from the right table and only matching records from the left table. Unmatched rows from the right table are retained with null values from the left table.

A *Full Outer Join* returns all records from both tables. When a matching value exists in both tables, the records are combined. If a record exists in only one table, it is still included, and the columns from the other table contain null values.

An Anti Join returns only the records that do not have a match in another table. It is commonly used to find missing or unmatched records

There are two types of Anti Joins

A Left Anti Join returns only the rows from the left table that do not have a matching record in the right table. It is useful for identifying missing transactions, unsold products, or customers without orders. (Think of it as "show me records in Table A(left) that missing in Table B(right)").

A Right Anti Join returns only the rows from the right table that do not have a matching record in the left table. This join is often used for data quality checks and finding orphan records.("show me records in Table B(right) that are missing in Table A(left)")

Left Anti Records only in the left table
Right Anti Records only in the right table