Data Modelling, Relationships & Joins in PowerBI.

Introduction

Data modeling is the foundational chapter of any power BI project; if the model is weak, the entire project including performance, calculations, and maintenance will suffer. the following article outlines the core concepts of data modelling.

Data modeling

This is the process of defining how business data is structured and how different pieces of information relate to each other, it involves organizing raw data into logical tables, establishing relationships between them and creating calculated measures using DAX to answer business questions.
Components of a data model in power BI include;

1. Tables - entities that store structured data.
2. Relationships - connections that define how filters and data flow between tables.
3. Calculations - DAX measures and calculated fields created on top of the model to compute metrics eg; total sales or average profit.

Importance of well designed Data model

A well optimized data model serves 2 goals;

1. Organizing data around business objects
2. Ensuring power BI operates with maximum efficiency through;

• Reporting and Analytics - A well structured data model separates numeric facts from descriptive contex allowing report creators to easily slice, filter and group metrics.
• DAX calculation - Proper table structures and relationships reduce the complexity of DAX code.
• Performance - A well designed data model ensures fast report visual rendering, quick slicer interaction, and responsive query processing.
• Scalability - A well designed model scales cleanly without causing excessive memory consumption or system slow downs.
• Maintanability - A well structured data model establishes a clear starting point and intuitive architecture.

Data modeling approaches

1. Flat table
Also called (single table), it combines all transactional events and descriptive business entities into one single denormalized table without establishing any relationships.
Every row contains both numeric metrics and repetitive text descriptions of the associated customer, product or location.

Structure - There is only one table, columns that would normally live in separate dimension tables are repeated on every row alongside the transaction measures .

Advantages include;

• It is simple to build.
• There is no relationship to configure or debug.
• It is fast for small datasets
• It is easy to export and use by non technical users.

Disadvantages include;

• Massive redundancy, repeated attribute values bloat file size
• Update anomalies changing one customers city means editing every row for the customer.
• Hard to maintain data integrity or enforce consistent attribute values.
• Doesn't scale well as data volume or complexity grows.
• Poor compressions in columnar engines (like Power BI's VertiPaq) because repeated text columns don't compress as well as normalized dimension keys.

A flat table is suitable only for small, single source ad-hoc reports, rapid prototyping, or simple exports where relational complexity is unnecessary.

A flat table implicates performance on power BI through poor performance on large datasets because power BI's compression and query engines are optimized for relational dimensional models, not massive unsplit flat tables.
It also implicates model complexity through low structural complexity (only one table), but high calculation and maintenance complexity.

2. Star schema
A star schema is a dimensional modeling architecture where a central Fact Table holding transactional metrics is directly surrounded by independent Dimension Tables, forming the visual shape of a star.

One fact table holds transactional or event-level data and foreign keys pointing to each dimension. Each dimension table is flat (not further normalized) e.g., the Product dimension contains ProductName, Category, and Subcategory all in one table, rather than splitting Category into its own table.

Advantages include;

• Simple, intuitive relationships easy for report authors to navigate.
• Very fast query performance; VertiPaq compresses low-cardinality dimension columns efficiently.
• Fewer joins than a snowflake, so DAX filter propagation is fast and predictable
• Well-supported best practice model for Power BI, Tabular models, and most BI tools
• Easier to explain to business users (facts vs. descriptive attributes)

Disadvantages include;

• Some redundancy remains within each dimension (e.g., Category repeated per product).
• Larger dimension tables can be harder to maintain if source data is highly normalized.
• Not ideal for very deep hierarchies with many-to-many sub-attributes.

A star schema is the default choice for almost all Power BI or analytical reporting models for sales, finance, operations dashboards, and most business reporting scenarios where performance and simplicity both matter.

It has implications on;
Power BI Performance as Fast query response times, optimal memory compression, and highly efficient filter context handling.
Model complexity as Low-to-moderate structural complexity; clean, intuitive, and highly maintainable.

3. Snowflake Schema
A snowflake schema is a star schema whose dimension tables are further normalized into multiple related sub-dimension tables, removing redundancy within the dimensions themselves.
The result looks like a star with each point branching further outward.

The fact table still sits at the center, but instead of one flat "Dim_Product" table, product attributes are split for example, Dim_Product links to a separate Dim_Category table, which may link to a separate Dim_Department table. Each level is normalized: each attribute is stored once, in its own table, referenced by key.

Advantages include;

• Minimizes redundancy within dimension attributes as each value stored once.
• Enforces stronger referential or data integrity for shared hierarchy attributes.
• Can reduce storage for very large, highly repetitive attribute sets in traditional (non-columnar) databases.
• Mirrors normalized source or OLTP systems, which can simplify ETL from those sources.

Disadvantages include;

• More tables and relationships to maintain and document.
• Extra joins or hops mean more complex, sometimes slower DAX filter propagation.
• Harder for report authors to navigate the model
• In Power BI specifically, the storage savings rarely matter as VertiPaq already compresses a flat dimension almost as well.

It is Reserved for extreme cases where dimension tables are massive and memory optimization is mandatory.

A snowflake schema has implications on;
Power BI Performance as slower query execution compared to Star Schema due to multi-table joins and indirect filter propagation.
Model Complexity is High structural complexity with numerous interconnected relationships.

Fact Tables

Fact Tables record business events, transactions, or activities answering what happened. They contain surrogate or foreign keys to connect to dimensions, timestamps or date keys, and numeric values such as quantity, sales amount, discount, cost, and profit used for mathematical calculations.
A fact table captures the quantitative results of business operations. Each row represents a specific historical event or transaction. Normally, a fact table contains four key elements:

1. Transaction Identifiers - Unique order or invoice IDs to identify the event.
2. Foreign Keys (IDs) - Key columns that link the transaction to surrounding dimension tables e.g. CustomerID, ProductID, StoreID.
3. Date and Time Information - Timestamps or date keys recording exactly when the business activity occurred e.g. OrderDate, ShipDate.
4. Numeric Metrics/Measures - Quantitative values used for mathematical aggregations and calculations e.g. Quantity, SalesAmount, Discount, UnitCost, Profit. Examples of a fact table include;

• FactSales - Individual item sales, transactions, quantities, and revenue totals.
• FactOders - Header level order totals,, shipping costs and order dates.
• FactTransactions - Inventory movements, bank deposits, or ledger entries.

Dimension Tables

Dimension Tables store descriptive attributes that provide context to facts answering who, what, where, and when.
Each row represents a unique business entity e.g. a specific product or customer with descriptive attributes such as product name, category, brand, or customer region used for filtering and grouping in reports.

A dimension table holds the master descriptive data for a single business entity. Each row represents one unique object for instance, exactly one product or one customer. A dimension table contains:

1. Primary Key (ID) - A unique identifier for every record e.g. ProductID or CustomerID.
2. Descriptive Attributes - Textual or categorical details describing the object e.g. ProductName, Category, Subcategory, Brand, Color, CustomerName, Region, City.

Examples of dimension tables include;

• DimCustomer - Unique customer profiles, email addresses, demographics and customer segments.
• DimProduct - Master product list, categories, subcategories, colors and unit prices.
• DimDate - Comprehensive calendar table listing dates, years, quarters, months and fiscal periods.
• Dimlocation - Store location names, addresses, cities, states, and regions.

Measures/numeric business events versus descriptive attributes.

The core functional distinction comes down to numbers versus labels:
Numeric Business Events (Measures) - Located in fact tables, these are continuous numeric measurements meant to be aggregated using mathematical operations such as SUM, AVERAGE, COUNT, or MIN/MAX. For example, SalesAmount is a numerical value that makes sense to sum across multiple sales.
Descriptive Attributes - Located in dimension tables, these are textual labels, categories, or dates used to slice, filter, and group the numeric metrics in reports. For instance, placing Category from a dimension table onto a chart axis groups the SalesAmount numbers from the fact table to generate a Sales by Category visual.

An example illustrating how a central fact table can be connected to several dimension tables to form a star schema.

Imagine a retail chain analyzing sales performance. FactSales sits at the center, with one row per line item sold grain: one row per product, per order, per store, per day. Each row carries the numeric measures Quantity, SalesAmount and foreign keys pointing outward to four dimensions:

DimDate — when the sale happened (year, month, quarter)
DimCustomer — who bought it (name, segment, city)
DimProduct — what was bought (name, category, brand)
DimLocation — where it was sold (store, region, country)
A report author can then drag SalesAmount from the fact table onto a visual, slice it by Category from DimProduct and Region from DimLocation, and Power BI's engine aggregates the numeric fact rows according to the filters coming from each connected dimension.

Relationships in power BI

In Power BI, a relationship is a logical connection established between two tables using a common key column. Relationships define how data in one table relates to data in another, allowing Power BI to automatically propagate filters, aggregate numbers, and perform cross-table analysis.

When building analytical models raw data is split across multiple tables—separating numeric transactional activities facts from descriptive attributes dimensions. Without relationships Power BI would treat each table as an isolated island requiring complex DAX logic or manual joins to combine metrics with descriptive categories. Relationships enable a clean relational structure where slicers and filters applied to context tables automatically adjust the metrics calculated in transaction tables.

Core foundational concepts of relationships in power BI include;
Primary Key (PK) - A column in a table typically a dimension where every value is strictly unique and non-null. It serves as the definitive identifier for each unique record e.g., CustomerID in DimCustomer or ProductID in DimProduct.
Foreign Key (FK) - A column in another table typically a fact table that references the Primary Key of a dimension table. Unlike Primary Keys, Foreign Key values repeat across multiple rows whenever an event occurs multiple times e.g., CustomerID appearing in FactSales.
Unique Values - The requirement on the "1" side of a 1-to-Many relationship where every key value appears exactly once. If duplicate keys exist on the dimension side, Power BI cannot establish a standard 1-to-Many relationship.
Cardinality - Refers to the numerical ratio and direction of matching records between two related tables. It dictates whether keys are unique on one side (1:), unique on both sides (1:1), or non-unique on both sides (:).
**Referential Integrity* - This is the data quality principle ensuring that every Foreign Key value in a fact table has a corresponding Primary Key match in the connected dimension table. If a sale contains a CustomerID that does not exist in DimCustomer, referential integrity is violated, creating a blank row in visuals.
Active Relationship - This is the primary default connection between two tables. Filters automatically flow through active relationships in visuals and DAX calculations. Only one active relationship can exist between any two tables at a time.
*Inactive Relationship *- Secondary connections created when multiple relationships exist between the same two tables for example, connecting FactSales[OrderDate] and FactSales[ShipDate] both to DimDate[Date]. Inactive relationships do not propagate filters automatically; they must be explicitly activated in DAX measures using the USERELATIONSHIP() function.

Why CustomerID might contain unique values in a DimCustomer table but
appear multiple times as a foreign key in a FactSales table.
In DimCustomer CustomerID is a Primary Key containing strictly unique values because each row describes one specific customer. In FactSales CustomerID acts as a Foreign Key appearing multiple times. This occurs because a single customer can place dozens of orders over time each order logs the customer's ID alongside transaction metrics like sales amount and quantity.

One-to-Many (1:*) Relationship
A single record in Table A the dimension table connects to zero, one, or multiple records in Table B the fact table. The key on the "1" side contains strictly unique values, while the key on the side contains repeating values.
For example
Connecting DimCustomerCustomerID to FactSalesCustomerID. Selecting a customer in a slicer filters the sales table down to only orders placed by that specific customer.

When to use - This is the gold standard default for Power BI models. Use 1:* to connect dimension tables to fact tables in a Star Schema.
When NOT to use - Do not use if key columns on both sides contain repeating duplicate values.

One-to-One (1:1) Relationship
Each row in Table A matches at most one row in Table B and vice versa. Key values in both columns are strictly unique.
For example;
An Employee table and an EmployeeConfidential table containing salary or ID number split apart for security/access reasons, each keyed on the same unique EmployeeID.

When to use- Useful when splitting off sensitive or security-restricted attributes into a separate table for Row-Level Security (RLS) or isolating secondary attributes that are rarely queried.
When NOT to use - Avoid using 1:1 relationships for standard dimension modeling. In almost all cases, two tables with a 1:1 relationship should be merged into a single dimension table in Power Query to eliminate unnecessary model overhead.

Many-to-Many (:) Relationship
Neither table's key column contains unique values; duplicate keys exist on both sides of the relationship.
For example
A joint bank account scenario — DimCustomer to DimAccount, where one customer can hold multiple accounts and one account (a joint account) can be held by multiple customers. Neither CustomerID nor AccountID is unique in the bridging structure.

When to use - Valid for handling complex multi-valued relationships e.g., bank accounts shared by multiple customers using a intermediate bridge table.
When NOT to use - Never connect two fact tables directly using a Many-to-Many relationship. Fact tables should always be connected through a shared dimension table such as a shared DimProduct or DimDate via clean 1:* relationships to form a Star Schema or Galaxy Schema.

Filter Direction

When a user selects a value in a slicer, clicks a visual element, or applies a report filter Power BI evaluates the active relationships connected to that table. The filter context flows across the relationship to restrict rows in related tables before evaluating DAX measures.

Single-direction filtering

In single-direction filtering, filters flow strictly from the dimension table to the fact table.

Both/Bidirectional filtering
Bidirectional filtering allows filters to flow in both directions across a relationship dimension filters fact, AND fact filters dimension.
While bidirectional filtering may seem convenient, it presents serious design risks:

• Ambiguous Filter Paths - Enabling bidirectional filtering across multiple tables creates circular filter loops. Power BI cannot determine which relationship path to evaluate, leading to model errors or inaccurate calculation results.
• Unintended Visual Filtering - Selecting a customer might unintentionally filter slicers for products or dates, confusing report end-users.

Example to demonstrate how selecting a value from a dimension such as DimProduct can filter records in FactSales.
Selecting "Laptops" in a slicer from DimProduct[Category] propagates a filter through the 1:* relationship to FactSales. FactSales is immediately filtered down to only display transaction rows matching laptop product IDs. However, filtering FactSales does not filter DimProduct.

Joins in Power Query

A join is a way of combining rows from two tables based on a matching key column in Power BI, this is done in Power Query using Merge Queries. Rather than manually looking up values, a merge lets you say match this table to that table wherever these key columns are equal and Power Query decides which rows to keep based on the join type you choose.
Example

Carol and David have no orders and order 104 belongs to CustomerID 5, who doesn't exist in Customers. This lets every join type produce a visibly different result.

Inner Join

How it works - Keeps only rows where the key exists in both tables no nulls, no unmatched rows.
Records retained - Matches only.
Expected result

When to use it - When you only want records that genuinely have a counterpart on both sides e.g., analyzing customers who have actually placed orders.
When not to use it - Avoid it if you need to preserve customers with zero orders for a churn or "never purchased" analysis, an inner join would silently delete exactly the rows you care about.

Left Outer Join

How it works - Keeps all rows from the first (left) table, plus any matching rows from the second (right) table. Where there's no match, the right-side columns come back as null.
Records retained - Everything from the left table, matched data attached where it exists.

Expected output Customers = left table

When to use it - This is the most common join type in real reporting scenarios e.g., you want every customer in a report, even ones with no orders yet.
When not to use it - Don't use it if unmatched rows with nulls will break downstream calculations like a SUM expecting numeric values unless you handle the nulls afterward.

Right Outer Join

How it works - The mirror image of a left join keeps all rows from the second (right) table, plus matches from the first (left) table where they exist.
Records retained - Everything from the right table, matched data attached where it exists.

Expected output (Customers = left, Orders = right)

When to use it - Useful when the driving table is really the second one selected e.g., you want every transaction, and you're attaching customer details onto it. In practice, most people just swap table order and use a left join instead, since it's more intuitive.
When not to use it - Rarely needed as a distinct choice in Power Query it's functionally identical to a left join with the tables swapped, so use whichever reads more naturally for the table order you already have.

Full Outer Join

How it works - Keeps every row from both tables, matched where possible, with nulls filled in on whichever side has no counterpart.
Records retained: The union of both tables.

Expected output:

When to use it - Useful for data-quality checks spotting orphaned records on either side of a relationship in one pass customers with no orders and orders with no customer.
When not to use it - Rarely used in a final reporting model, since the resulting nulls on both sides usually need cleanup before the table is analysis-ready.

Left Anti Join

How it works - Keeps only rows from the left table that have no match in the right table. No columns from the right table are brought in this is purely a filter.
Records retained - Left-only, unmatched rows.

Expected output:

When to use it - Perfect for "find the gaps" questions customers who've never ordered, products that have never sold, employees with no assigned manager.
When not to use it - Not useful if you actually need the matched rows too it deliberately excludes them.

Right Anti Join

How it works - The mirror of a left anti join keeps only rows from the right table that have no match in the left table.
Records retained: Rightonly, unmatched rows.

Expected output:

When to use it - This is the classic "orphaned transaction" check — orders, payments, or events referencing a key that no longer (or never did) exist in the reference table, which is exactly the kind of referential integrity break we talked about earlier.
When not to use it - Not useful when you need the matched data too, since it deliberately discards it.

Power Query Joins vs Power Bi Relationships

Understanding the distinction between merging (joining) tables in Power Query and creating relationships in the Power BI Data Model is one of the most fundamental concepts in Power BI architecture. While both operations link data across tables, they operate at completely different stages of the Power BI workflow, alter physical data structures differently, and serve distinct modeling purposes.

Does a Power Query merge physically combine data?

Yes. A merge in Power Query is a physical, row-level combination it's the direct equivalent of a SQL join. When you merge Orders with Customers on CustomerID, Power Query actually looks up matching rows and writes the matched columns directly into the output table e.g., CustomerName becomes a real column sitting inside Orders. The two source queries still exist independently, but the merged query is a brand-new table containing physically combined data new rows, new columns, actual duplicated values on disk/in memory.

Does creating a relationship combine the tables?

No — and this is the core distinction. A relationship in the Power BI data model is not a data operation at all. It doesn't touch, copy, or duplicate any values. It's a stored instruction these two columns can be matched that the DAX/VertiPaq engine uses at query time, every time a visual needs to combine data across tables. Customers and Orders remain two separate tables in the model, exactly as they were loaded, and Power BI joins them live, in memory, only for the duration of each query.

The stage at which Power BI operation workflow occur

When to choose a merge instead of a relationship

Merges make sense when you need to reshape data before it becomes a table in the model, not to link tables that will remain separate analytical entities. Typical reasons:

Bringing in a lookup value from a reference table permanently e.g., pulling a TaxRate or ExchangeRate column into a transactions table so it's available for row-level calculation logic that can't rely on relationship filter propagation.
Flattening a snowflaked source merging Category into Product before load, so the model receives a single, flat dimension as recommended in the star vs. snowflake discussion earlier.
Combining files with different structures e.g., appending or merging exports from two systems into one clean table before anything reaches the model.
Deduplication or enrichment logic that needs to happen once, in ETL, rather than being recalculated by every visual.

By contrast, use a relationship whenever the two tables represent genuinely distinct entities that should stay independently filterable and reusable across many visuals which, in practice, is almost every fact-to-dimension link in a normal report.

How excessive merging affects the model

If you merge everything upstream flattening every dimension directly into the fact table rather than relating them — you end up rebuilding a flat table by another name (the very structure discussed as a poor Power BI performer earlier). Specific problems:

• File size and refresh time balloon, since merged columns duplicate dimension text across every fact row instead of letting VertiPaq compress a small, separate dimension table.
• You lose reusability — a DimDate merged into five different fact tables can't be used as a single shared slicer across all five; each fact table carries its own disconnected copy of date attributes.
• Loss of flexibility - relationships can be toggled active/inactive, support bi-directional filtering, and let you build "what-if" or role-playing dimensions (like OrderDate vs ShipDate) — none of which is possible once the data's already flattened into one table.

• Harder maintenance - updating a category name means re-running ETL and refreshing a wide fact table, instead of just editing one row in a small dimension table.
  Reasons for keeping fact tables and dimension tables separate

• Centralized Logic & Reusability - A well-structured star schema model built around business domains can serve as a single source of truth for multiple reports. Changes made in one central model automatically propagate standards across all dependent reports.

• Drastically Simplified DAX - Because filters naturally flow from surrounding dimension tables down to the central fact table through relationships, DAX measures become concise and straightforward (e.g., Total Sales = SUM(FactSales[LineTotal])) without needing complex override logic.

• Optimized Engine Performance - When filtering a report visual such as selecting a Product Category in a slicer, Power BI scans a small, lightweight dimension table instantly rather than scanning millions of transaction rows.

Recommended Power BI Model

For a typical BI project, I'd recommend a star schema as the default, with snowflaking used only selectively for specific hierarchies that genuinely need it, and a flat table avoided except for very small, one-off datasets. Here's the reasoning against each factor:

• Query and report performance. VertiPaq compresses columns most efficiently when dimension attributes are isolated in small, low-cardinality tables and the fact table is kept narrow (mostly keys and numbers). A star schema is exactly this shape, so it consistently outperforms both a flat table (which forces massive redundant text into every row) and a snowflake (which adds extra join hops the engine has to traverse for every filter).
• DAX simplicity. DAX measures written against a star schema are shorter and easier to reason about, because filter context only has to travel one hop from dimension to fact. Snowflaking forces filters through multiple tables before reaching the fact table, which makes measures involving RELATED, multi-level hierarchies, or CALCULATE modifiers noticeably harder to write and debug correctly.
• Model readability - A star schema's diagram is genuinely legible at a glance a fact table with dimensions radiating outward. A snowflake's extra branching sub-dimensions make the model view cluttered, and a flat table, while trivially readable as a single table, hides all structure and forces users to scroll through dozens of repeated columns to understand what's actually being measured versus described.
• Scalability - As row counts and business complexity grow, a star schema scales gracefully because the fact table — the fastest-growing table stays lean. A flat table's redundancy compounds with every new row, and a snowflake's extra relationship hops compound with every new sub-dimension, both of which degrade faster than a star schema under growth.
• Data redundancy - A snowflake technically minimizes redundancy the most, but as discussed earlier, VertiPaq already compresses a flat dimension so efficiently that the storage savings rarely justify the added complexity. A star schema accepts a small, deliberate amount of redundancy within each dimension in exchange for simplicity — a reasonable trade in a columnar engine. A flat table has redundancy at the far end of the spectrum and gains nothing from it.
• Maintainability - Fewer tables and shallower relationships mean fewer things to break. A star schema is easy to extend (add a new dimension, add a new fact table sharing existing dimensions) without restructuring what's already there. A snowflake requires maintaining referential integrity across more tables and relationship hops; a flat table requires re-engineering the whole structure any time the granularity or a shared attribute needs to change.
• Ease of creating reports - Report authors work with field lists and drag-and-drop visuals — a star schema maps naturally onto that mental model measures from the fact table, slicers/axes from the dimensions. A snowflake forces authors to know which sub-table an attribute lives in; a flat table offers everything from one list but with no structural guidance on what's a measure versus a filter.
• Filter propagation - This is where the star schema's advantage is cleares with single-hop, one-to-many relationships, filter context propagates predictably and quickly from dimension to fact. Multi-hop snowflake relationships introduce more places where propagation can behave unexpectedly, especially if any relationship along the chain is set to bi-directional.
• Model complexity - Lowest complexity that still supports proper analytical modeling. A flat table is simpler only in the trivial sense of having one table it has no real modeling structure at all, which becomes a liability rather than a virtue once you need real reporting flexibility.

Relationships and filter direction

For the star schema itself, I'd implement:

• One-to-many (1:*) relationships from each dimension to the fact table, as the default and near-universal choice each dimension's key is unique, each fact row references it once.
• Single-direction filtering (dimension → fact) as the default for every relationship, since that matches how star schema filtering is meant to work and avoids the ambiguous, sometimes duplicated-aggregation behavior that bi-directional filtering can introduce.
• Bi-directional filtering reserved for specific, deliberate cases most commonly a many-to-many bridge table (e.g., linking FactSales to a promotions or sales-channel bridge), where filtering needs to flow both ways to work correctly. I would not turn on bi-directional filtering broadly across the model just in case.
• Inactive relationships plus USERELATIONSHIP() for any case where a fact table needs to relate to the same dimension more than once the classic example being OrderDate and ShipDate both pointing to DimDate. Keep one active by default, and invoke the second explicitly inside specific measures.
• Many-to-many relationships resolved through a bridge table wherever feasible, rather than direct M:M links, since bridge tables reduce to two well-behaved one-to-many relationships and give far more predictable performance and filter behavior.