Data Modelling, Relationships & Joins

Data Modelling in Power BI

Coming into this, I assumed "data modelling" just meant loading a table into Power BI and building charts on top of it. What it actually means is deciding how your tables relate to each other before you ever touch a visual because that structure is what every DAX calculation, filter, and report performance number depends on downstream. A messy model doesn't just look ugly in the background, it makes calculations slower, filters behave unpredictably, and reports harder to maintain as the business grows.

There are three common approaches to structuring this:

• flat tables
• star schemas
• snowflake schemas.

Flat table is the simplest structure, every piece of data lives in one wide table. A sales record might repeat the customer's name, region, and product category on every single row, because nothing is broken out separately.

Advantages:

• No relationships to configure
• Dead simple to build for tiny datasets
• Easy for a beginner to understand at a glance

Disadvantages:

• Massive redundancy: the same customer name gets typed thousands of times
• Slower performance as row count grows
• DAX measures get harder to write cleanly, since everything is tangled into one table

When it's appropriate:

• Very small, one-off analyses
• A dataset that will never grow or be reused
• Quick exploratory checks, not a real report

Performance impact:

• Power BI's engine (VertiPaq) compresses data column by column, so repeated values do compress individually
• But forcing one column to carry mixed responsibilities hurts both compression and calculation clarity as the model scales

SaleFlat
Sale ID Category
CustomerName Quantity
ProductName Revenue
Region OrderDate

Star Schema

A star schema separates data into one central fact table (the measurable events: sales, orders) surrounded by dimension tables (descriptive context: customers, products, dates, locations), each connected directly to the fact table.

Advantages:

• Minimal data redundancy
• Fast performance: Power BI's engine is optimized specifically for this shape
• Simple DAX measures
• Easy to understand and extend as the model grows

Disadvantages:

• Requires upfront design effort, you have to decide what's a "fact" vs a "dimension"
• Slightly more setup than just dumping everything into one table

When it's appropriate:

• Almost always, for any real Power BI report
• It's the industry recommended default schema

Performance impact:

• This is the exact shape Power BI is built around
• Filters propagate cleanly across one relationship hop
• DAX measures like SUM(FactSales[Revenue]) stay simple and fast

Snowflake Schema

A snowflake schema takes a star schema further by normalizing dimensions, splitting a dimension into multiple related tables. For example, DimProduct might split into DimProduct and DimCategory, so category names aren't repeated across every product row.

Advantages:

• Even less data redundancy than a star schema
• Useful when a dimension has a lot of repeated sub-attributes

Disadvantages:

• More tables means more relationships to manage
• Adds complexity to the model
• Extra joins during query execution can slow performance

When it's appropriate:

• Large enterprise models where a dimension is genuinely large and repetitive enough that normalizing it saves meaningful storage or performance
• Otherwise it's often unnecessary complexity for a student or small-business project

Performance impact:

• Generally slightly slower than a pure star schema
• Power BI has to traverse an extra relationship hop to get from the fact table to the outer dimension table

Fact Tables and Dimension Tables

Once I understood the star shape, the next question was, how do I actually decide which table is the "fact" and which ones are "dimensions"?. Facts are things that happened, dimensions are things that describe what happened.

Fact Tables

A fact table stores the measurable events of the business, the numbers you actually want to add up, average, or count.

What's normally stored in a fact table:

• Numeric measures (Quantity, Revenue, Profit)
• Foreign keys linking to each related dimension table
• A timestamp or date key for when the event occurred

Measures vs. descriptive attributes:

• Measures are numbers meant to be aggregated, summed, averaged, counted (e.g. Revenue, UnitsSold)
• Descriptive attributes are labels that explain the measure but aren't themselves calculated (e.g. CustomerName, ProductCategory),these belong in dimension tables, not the fact table

Grain / granularity:

Grain means what one single row in the fact table actually represents
Example: if each row is one line item on an order, the grain is "one product per order." If each row is one full order regardless of how many products it contains, the grain is "one order"
Getting the grain wrong early causes major rework later, since every measure and relationship is built assuming a specific row-level meaning

Examples of fact tables:

• FactSales: one row per sale
• FactOrders: one row per order
• FactTransactions: one row per financial transaction

Dimension Tables

A dimension table stores the descriptive context that explains who, what, where, and when around a fact.

What's normally stored in a dimension table:

• Descriptive attributes (names, categories, addresses)
• A unique key that identifies each row (e.g. CustomerID)
• Attributes used for filtering and grouping reports, not for calculation

Examples of dimension tables:

• DimCustomer: customer name, region, contact info
• DimProduct: product name, category, brand
• DimDate: day, month, quarter, year
• DimLocation: city, country, store branch

Practical Example: Connecting Fact to Dimensions

Using the diagram we used earlier, a central FactSales table records every sale, quantity, revenue, and foreign keys pointing to a customer, a product, a date, and a location. It doesn't store the customer's name or the product's category directly; it just stores the keys pointing to those details.

• DimCustomer connects to FactSales so every sale can be traced - back to who bought it
• DimProduct connects to FactSales so every sale can be traced back to what was sold

Relationships in Power BI

A relationship is what tells Power BI how two tables connect specifically, which column in one table matches which column in another. Without relationships, every table would sit isolated, and Power BI would have no way to combine a sale in FactSales with the customer who made it in DimCustomer. Relationships are what let a star schema behave like one connected model instead of a pile of unrelated spreadsheets.

One-to-Many (1:*)

How it works:

• One row on the "1" side can match many rows on the "many" side
• This is the default and most common relationship type in Power BI

Example:

One customer in DimCustomer can appear in many rows of FactSales (a customer can make multiple purchases).

When to use it:

• Anytime a dimension table describes many events in a fact table, this is the backbone of every star schema

When not to use it:

• Don't force a 1:* relationship between two tables that don't actually have that structure; it will silently produce wrong aggregations (e.g., inflated totals) if the "1" side isn't actually unique

One-to-One (1:1)

How it works:

• Exactly one row in Table A matches exactly one row in Table B, with no repeats on either side

Example:

Employee and EmployeeBadge: Each employee has exactly one badge, and each badge belongs to exactly one employee

When to use it:

• Rare in practice, mostly used when splitting one table into two for organizational or security reasons (e.g., separating sensitive HR data into its own table)

When not to use it:

Don't use 1:1 just because two tables happen to be the same size, confirm the actual relationship is genuinely one-to-one, not coincidentally matching row counts.

Many-to-Many (:)

How it works:

• Multiple rows in Table A can match multiple rows in Table B, with no single "unique" side.

Example:

• Students and Courses: One student takes many courses, and one course has many students enrolled

When to use it:

• Only when the real-world relationship genuinely has no unique side. Power BI supports this, but it should be a deliberate choice, not a shortcut

When not to use it:

• Avoid it as a workaround for poor table design; many-to-many relationships make filter behavior harder to predict and can silently produce incorrect totals if not modeled carefully Keys, Uniqueness, and Referential Integrity

Primary Keys:

• A column that uniquely identifies each row in a table (e.g. CustomerID in DimCustomer)
• Every value in this column must be unique, no duplicates allowed

Foreign Keys:

• A column in another table that references a primary key elsewhere (e.g., CustomerID in FactSales)
• These values are allowed to repeat, since one customer can have many sales

Why CustomerID is unique in DimCustomer but repeats in FactSales:

In DimCustomer, CustomerID is the primary key, each customer appears exactly once
In FactSales, CustomerID is a foreign key, it appears once per sale, so the same customer's ID shows up on every row representing their purchases

Referential integrity:

Every foreign key value should have a matching primary key somewhere. A CustomerID in FactSales should always correspond to a real customer in DimCustomer
When this breaks (a foreign key with no match), Power BI treats those rows as "unknown" in visuals, which can silently distort a report if left unnoticed.

Active vs. inactive relationships:

Power BI only allows one active relationship between two tables at a time; this is the one used automatically in calculations.
Additional relationships between the same two tables can exist but must be marked inactive and are only used when explicitly called with USERELATIONSHIP() in a DAX formula
A common example: a FactSales table might have both an OrderDate and a ShipDate, both relating to ``, but only one can be active by default, so the other stays inactive until a specific measure needs it.

Filter Direction

Once tables are related, the next question is: when I click on something in one table, which other tables actually respond to that click? That's what filter direction controls, it determines which way a filter is allowed to "travel" across a relationship.

Single-Direction Filtering

How it works:

• A filter only flows from the "1" side of a relationship to the "many" side
• This is Power BI's default behavior for relationships in a star schema

Example:

• Selecting a product in DimProduct filters FactSales down to only the sales rows for that product
• But selecting or filtering something in FactSales does not filter DimProduct back, the dimension table stays fully visible

Why this is the safe default:

• It matches how a star schema is meant to work, dimensions describe facts, not the other way around
• It keeps filter behavior predictable and easy to reason about Bidirectional Filtering

How it works:

The filter flows both ways, selecting something in the fact table can also filter the dimension table, and vice versa

Example:

• Useful in a many-to-many scenario, like filtering a Students table based on a selection in a Courses table, where neither table is a clear "dimension" or "fact"

Why it should be used carefully:

• Ambiguous filter paths: if a model has multiple bidirectional relationships forming loops between tables, Power BI can no longer determine a single clear path for a filter to follow, which can cause errors or force it to pick an unintended path
• Unnecessary model complexity: turning on bidirectional filtering everywhere "just in case" makes the model harder to debug, since every filter interaction now has more possible directions to trace
• Performance cost: filters traveling in both directions across many relationships require more computation than a clean single-direction star schema

Practical rule of thumb: only turn on bidirectional filtering when you have a specific, deliberate reason (like a genuine many-to-many relationship), never as a default setting across your whole model.

Joins in Power Query

A join is how you combine two tables based on a matching column, using Merge Queries in Power Query. Unlike a Power BI relationship, a merge actually pulls columns from one table into another during the data-loading stage, before the model is even built.

Using my Customers table (6 customers, two of whom, Grace and Mary, have never placed an order) and Orders table (7 orders, one of which references a CustomerID that doesn't exist in Customers), here's what each join type produces:

Inner Join

• Definition: keeps only rows where the matching column exists in both tables
• Retained: customers 1–4 and their matching orders
• Result: 6 rows Grace, Mary, and the orphaned order (106) are all dropped

Left Outer Join

• Definition: keeps every row from the left (first) table, plus matches from the right table where they exist
• Retained: all 6 customers, with order details attached where available, blank/null where not
• Result: 8 rows. Grace and Mary appear once each with null order fields

Right Outer Join

• Definition: keeps every row from the right (second) table, plus matches from the left table where they exist
• Retained: all 7 orders, with customer details attached where available
• Result: 7 rows; Order 106 appears with null customer fields

Full Outer Join

• Definition: keeps every row from both tables, matched where possible, null where not
• Retained: everything, matched rows, Grace, Mary, and Order 106
• Result: 9 rows total

Left Anti-Join

• Definition: keeps only rows from the left table that have no match in the right table
• Retained: customers with zero orders
• Result: 2 rows, just Grace and Mary

Right Anti-Join

• Definition: keeps only rows from the right table that have no match in the left table
• Retained: orders with no valid customer
• Result: 1 row, Order 106

Power Query Joins vs. Power BI Relationships

• A merge and a relationship both "connect" two tables, but they do fundamentally different things, at different stages of the workflow.

Does a Power Query merge physically combine data?

• Yes. A merge creates a genuinely new, combined table, the columns from both tables physically sit together in one result, as I saw when merging Customers and Orders
• This happens during data preparation, before anything is loaded into the model.

Does creating a relationship combine the tables?

• No. A relationship leaves both tables completely separate, DimCustomer and FactSales remain two distinct tables in the model
• The relationship just tells Power BI, "These two tables are linked by this key," so it can look up matching rows on demand when a report needs it.

At what stage does each happen?

• Merge in Power Query before the data is loaded into the model
• Relationship to the Data Model, after the data has already been loaded

When would you choose a merge instead of a relationship?

• When you genuinely need one flat, combined table for a specific export or a tool that can't handle relationships
• When a calculation is much simpler to express against one combined table than across a relationship
• Generally: rarely, for a proper BI model, most reporting scenarios are better served by relationships

How can excessive merging affect the model?

• Every merge creates a new flattened table, which reintroduces the same redundancy problem a star schema was designed to avoid, the same customer name or product category gets repeated across every row again
• A model full of merged tables starts looking like several disconnected flat tables instead of one clean star schema, making it harder to maintain and slower to query as data grows

Why keep fact and dimension tables separate instead of merging them?

• Smaller table sizes: DimCustomer stores each customer once, not once per sale
• Cleaner DAX: measures can reference the fact table's numbers and let relationships handle the descriptive context
• Easier maintenance: updating a customer's region means changing one row in DimCustomer, not thousands of rows in a merged mega-table

Practical example, side by side:

• If I merged Customers into Orders, I'd get one wide table where "Alice Wambui, Nairobi" is repeated on both her orders (101, 104), creating exactly the redundancy problem from flat table explanation.
• If I instead related the two tables, "Alice Wambui, Nairobi" is stored once in Customers, and both her orders in the fact table simply reference her CustomerID, no repetition, and Power BI resolves the connection only when a report actually needs it

Recommended Power BI Model

After working through data modelling, relationships, filter direction, and joins hands-on, my recommendation for a typical business intelligence project is a star schema, with single direction one-to-many relationships as the default. Here's the reasoning, broken down by the factors that actually matter in practice:

Query and report performance:

• Power BI's engine (VertiPaq) is specifically optimized around the star schema shape.
• Fewer relationship hops mean filters resolve faster, especially as data volume grows

####DAX simplicity:

• Measures like SUM(FactSales[Revenue]) stay simple when the fact table holds only numbers and keys
• Snowflake or flat structures force DAX to work around extra joins or repeated columns, adding unnecessary complexity to formulas

Model readability:

• A star schema is visually intuitive in Model View, one glance shows what's a fact and what's a dimension
• A flat table hides this structure entirely; a snowflake schema adds enough branching that it takes longer to read at a glance

Scalability:

• Adding a new dimension (e.g. DimSalesRep) to a star schema is a single new table and one new relationship
• A flat table would require adding and repeating a new column across every existing row instead

Data redundancy:

• A star schema keeps each customer, product, or date stored once, referenced by key, this is the single biggest advantage over a flat table
• Snowflake reduces redundancy even further, but for most business reporting needs, the star schema's redundancy is already low enough that the extra normalization isn't worth the added complexity

Maintainability:

• Updating a customer's region means editing one row in DimCustomer, not hunting through thousands of repeated rows in a flat table
• Fewer tables and simpler relationships (star) are easier to hand off to another analyst than a deeply normalized snowflake model

Ease of creating reports:

• Star schema's clean dimension-to-fact structure means report builders can drag fields into a visual and get correct, predictable aggregations without needing to understand complex relationship paths

Filter propagation:

• Single-direction filtering (dimension to fact) is predictable and matches how most business questions are asked, "show me sales by region," not "show me regions by sales."
• I would only enable bidirectional filtering in a specific, deliberate case, like a genuine many-to-many relationship, never as a blanket default.

Model complexity:

• Star schema hits the sweet spot between "too simple" (flat table, all redundancy) and "too complex" (snowflake, extra hops and relationships) for the vast majority of business reporting needs.

My default relationship design: one-to-many, single-direction, active relationships from each dimension table to the central fact table. I'd only introduce a snowflake normalization if a specific dimension were large and genuinely repetitive enough to justify it, and I'd only use bidirectional filtering or many-to-many relationships when a real business question required it never as a starting default.