Introduction
A Power BI report is only as good as the data model sitting underneath it. For instance, two analysts can start with identical raw data and end up with wildly different reports; one fast, easy to extend, and simple to write DAX against; the other slow, tangled, and fragile the moment a new requirement shows up. The difference almost always comes down to data modelling decisions made before a single visual was ever placed on a canvas.
This article will walks you through how Power BI models data using flat tables versus star and snowflake schemas, the role of fact and dimension tables, how relationships and filter direction actually work, the difference between a Power Query join and a Power BI relationship, and finally, a justified recommendation for how to structure a typical BI project.
1. Data Modelling in Power BI
Data modelling is the process of organizing your tables and the connections between them before you start building visuals. It answers questions like: should this be one big table, or several smaller ones? How do they relate to each other? Which table holds the numbers you want to measure, and which tables hold the descriptive context around those numbers?
This matters for several practical reasons:
1.DAX simplicity: a well-structured model lets a single measure like SUM(Sales[Amount]) work correctly across every report filter, without needing to be rewritten for each new chart.
2.Performance: Power BI's engine (VertiPaq) is optimized to compress and query well-structured, related tables far more efficiently than one enormous flat table.
3.Scalability: adding a new dimension (say, a Promotions table) to a well-modelled star schema is a five-minute job; retrofitting it into a flat table can mean rebuilding half your columns.
4.Maintainability: when business logic changes (e.g., a new way of categorizing customers), a well-modelled table only needs updating in one place, not duplicated across every report.
Flat Table
Definition: a single, wide table containing every piece of data-transactional facts and descriptive attributes in one place. This is the Excel-style approach: one row per transaction, with customer name, product name, region, date, and sales amount all sitting in the same row.
Structure: no separate tables, no relationships, just one table with many columns.
erDiagram
SALES_FLAT {
int OrderID
date OrderDate
string CustomerName
string CustomerCity
string ProductName
string ProductCategory
string Region
int Quantity
decimal SalesAmount
}Advantages:
1.Simple to understand at a glance.
2.No relationships to configure, so it's fast to build for very small, one-off analyses.
Disadvantages:
1.Massive data redundancy: if "Nairobi" appears as a customer's city on 500 orders, that text is repeated 500 times instead of stored once.
2.Poor performance at scale: a wide, repetitive table compresses far worse than normalized tables, and Power BI's engine has to do more work per query.
3.Difficult to maintain: updating a customer's city means finding and updating every row that customer appears in, rather than one row in a dimension table.
4.DAX becomes harder to reuse across contexts once a table conflates multiple grains (e.g., "one row per order" and "one row per customer" living together).
When appropriate: small, static datasets, quick one-time analyses, or genuinely simple use cases with no plans to scale. It's a reasonable starting point, but not something to scale a serious BI solution on.
Star Schema
Definition: a central fact table (holding measurable business events such as; sales, orders, transactions) surrounded by multiple dimension tables (holding descriptive context such as customers, products, dates, locations), each connected directly to the fact table by a relationship.
Structure: the fact table sits in the middle; each dimension connects to it independently, with no dimension connecting to another dimension.
erDiagram
DimCustomer ||--o{ FactSales : "CustomerID"
DimProduct ||--o{ FactSales : "ProductID"
DimDate ||--o{ FactSales : "DateKey"
DimLocation ||--o{ FactSales : "LocationID"
DimCustomer {
int CustomerID PK
string CustomerName
string Segment
}
DimProduct {
int ProductID PK
string ProductName
string Category
}
DimDate {
int DateKey PK
date FullDate
string Month
int Year
}
DimLocation {
int LocationID PK
string City
string Region
}
FactSales {
int OrderID
int CustomerID FK
int ProductID FK
int DateKey FK
int LocationID FK
decimal SalesAmount
int Quantity
}Advantages:
1.Eliminates redundancy: "Nairobi" is stored once in DimLocation, not once per transaction.
2.Fast query performance: Power BI's VertiPaq engine is specifically optimized for this shape.
3.Simple, predictable DAX: filters flow cleanly from dimension to fact in one hop.
4.Easy for report builders to understand and navigate.
Disadvantages:
1.Requires upfront design work: you need to identify your facts and dimensions before building, rather than just dumping in a flat export.
2.Slightly less intuitive for someone used to reading a single spreadsheet.
When appropriate: the default choice for the vast majority of real world Power BI projects: sales analysis, product performance dashboards, operational reporting, and anything expected to grow over time.
Performance and complexity implications: star schemas are what Power BI is built to perform best on. Model complexity is low and predictable, one hop from any dimension to the fact table and this directly keeps DAX formulas simple, since Power BI's automatic filter propagation does most of the work.
A worked example, built from a real dataset: to make this concrete, I took a Kenya crop production dataset that originally arrived as a single flat table — one row per crop record, with County, Crop Type, Season, Planting Date, Harvest Date, and a set of numeric measures (Revenue, Profit, Yield, Production Cost, Market Price) all sitting in the same wide table. I rebuilt it into a proper star schema in Power Query, splitting it into a fact table and four dimensions:
1.Kenya_Crops_Dataset (Fact) - Planted Area, Yield, Market Price, Production Cost, Revenue, Profit, plus Planting Date and Harvest Date
2.DimCounty - County
3.DimCropType - Crop Type
4.DimSeason - Season
5.Calendar - Date
Each dimension was created by referencing the original fact query, keeping only it's one relevant column, and removing duplicates, turning a flat table into a normalized set of tables ready to be related.
Figure 1: The finished star schema in Power BI's Model View. Note the two lines running to Calendar one solid, one dashed
Snowflake Schema
Definition: an extension of the star schema where dimension tables are further broken down into related sub-dimensions, rather than each dimension being a single flat table.
Structure: instead of DimProduct holding both product details and category details in one table, a snowflake schema splits this into DimProduct → DimCategory, connected to each other before either connects back to the fact table.
erDiagram
DimCategory ||--o{ DimProduct : "CategoryID"
DimProduct ||--o{ FactSales : "ProductID"
DimCity ||--o{ DimLocation : "CityID"
DimLocation ||--o{ FactSales : "LocationID"
DimCategory {
int CategoryID PK
string CategoryName
}
DimProduct {
int ProductID PK
string ProductName
int CategoryID FK
}
DimCity {
int CityID PK
string CityName
string Country
}
DimLocation {
int LocationID PK
int CityID FK
string Region
}
FactSales {
int OrderID
int ProductID FK
int LocationID FK
decimal SalesAmount
}Advantages:
1.Further reduces redundancy in cases where a dimension itself has repeating descriptive data (e.g., many products sharing the same category name and description).
2.Can better reflect a genuinely hierarchical business structure (Category → Sub-category → Product).
Disadvantages:
1.More tables means more relationships, meaning more "hops" a filter has to travel through to reach the fact table, this adds query overhead and can slow report performance compared to a star schema.
2.More complex DAX and more places for a broken relationship to hide.
3.Harder for report builders to navigate, finding the right field means knowing which of several linked tables it lives in.
When appropriate: large enterprise models with genuinely deep, reused hierarchies, or where a dimension is large enough that normalizing it meaningfully reduces storage and improves maintainability. It's a deliberate trade-off, not a "better" version of a star schema.
Performance and complexity implications: every additional join level is an additional cost. It is good to default to a star schema and only snowflake a specific dimension when there's a clear, measurable reason to.
2. Fact Tables and Dimension Tables
Fact tables store the measurable, numeric events of a business; the things you want to sum, average, or count. Think of sales transactions, orders placed, website clicks, or support tickets logged. A fact table is typically long with many rows and narrow few columns: mostly foreign keys pointing to dimensions, plus a handful of numeric measures.
Dimension tables store the descriptive context that explains who, what, where, and when around those facts - customer names, product details, dates, locations. Dimension tables are typically wide with many descriptive columns but short rows than the fact table.
| Fact Table | Dimension Table | |
|---|---|---|
| Stores | Measurable business events | Descriptive attributes |
| Example content | SalesAmount, Quantity, OrderID | CustomerName, ProductCategory, City |
| Typical shape | Many rows, few columns | Fewer rows, many columns |
| Examples | FactSales, FactOrders, FactTransactions | DimCustomer, DimProduct, DimDate, DimLocation |
Measures vs. descriptive attributes: a measure is a number you aggregate SUM(FactSales[SalesAmount]). A descriptive attribute is something you group or filter by, but never sum. Grouping sales by Region makes sense; summing "Region" does not.
Grain / granularity: this is one of the most important and most overlooked concepts in fact table design. Grain describes what a single row in the fact table represents. Is one row "one order" or "one line item within an order" or "one day's total sales for one product"? Every measure and every relationship depends on this being clearly defined and consistent - mixing grains in one fact table (some rows representing an order, others representing a daily summary) breaks aggregations silently.
Practical example, the Kenya Crops model: in the star schema built for Section 1, the grain of the fact table is one row per crop production record. Its numeric measures - Yield, Revenue, Profit, Production Cost, Market Price, are the facts. County, Crop Type, and Season are descriptive attributes, which is exactly why they were pulled out into their own dimension tables rather than left as repeating text in every fact row. Each dimension connects directly to the fact table - the star shape from Section 1 - letting a report slice total revenue or yield by any combination of county, crop type, season, or date, without duplicating that descriptive text across every row the way the original flat table did.
Figure 2: The fact table's grain, one row per production record visible here before it was split into the star schema. This single, wide table is the "before" state Section 1's flat-table discussion refers to.
3. Relationships in Power BI
A relationship is a defined connection between two tables, based on a shared column, that tells Power BI how rows in one table relate to rows in another. Relationships are necessary because splitting data into multiple tables (as a star or snowflake schema does) only works if Power BI has a way to reassemble the connections between them at query time - this is the direct successor to the manual XLOOKUP work in Excel, done once structurally instead of formula-by-formula.
One-to-Many (1:*)
How it works: one row in Table A can relate to many rows in Table B, but each row in Table B relates back to only one row in Table A. This is by far the most common relationship type in a star schema.
Example: one row in DimCustomer (a single customer) relates to many rows in FactSales (that customer's many orders). In the Kenya Crops model, the same pattern holds between DimCounty and the fact table: one county (e.g. "Nakuru") relates to many crop production records.
*Figure 3: Power BI's relationship dialog, showing the County relationship's cardinality.
When to use it: this is the default, expected relationship between any dimension and its fact table.
When not to use it: it shouldn't be used to connect two tables that both hold transactional-level data with no clear "one side" - that usually signals you actually have a many-to-many situation being modelled incorrectly.
One-to-One (1:1)
How it works: one row in Table A relates to exactly one row in Table B, and vice versa.
Example: a DimCustomer table split into DimCustomerProfile (name, segment) and DimCustomerContact (email, phone), where each customer has exactly one matching row in each.
When to use it: rare in practice - usually only when a table has genuinely been split for organizational or security reasons (e.g., separating sensitive contact details from general profile data).
When not to use it: if you find yourself creating a 1:1 relationship as a workaround, it's almost always a sign those two tables should simply be merged into one - a 1:1 relationship adds model complexity without a real modelling benefit in most cases.
Many-to-Many (:)
How it works: rows in Table A can relate to many rows in Table B, and rows in Table B can relate to many rows in Table A.
Example: a DimProduct table and a DimPromotion table, where one promotion can apply to many products, and one product can be part of many different promotions at once.
When to use it: when the real-world business relationship genuinely has no "one" side - this does happen, but it should be a deliberate modelling decision, often resolved with a bridge table in between rather than a direct many-to-many link.
When not to use it: many-to-many relationships are harder for Power BI to filter through efficiently and can produce ambiguous or unexpectedly duplicated results if used casually. Most textbook "many-to-many" cases are better modelled with an intermediate bridge table that breaks the relationship into two clean one-to-many hops.
Keys, Cardinality, and Integrity
1.Primary Key: a column that uniquely identifies each row in a table. e.g., CustomerID in DimCustomer, where every value appears exactly once.
2.Foreign Key: a column in another table that refers back to a primary key. e.g., CustomerID in FactSales, where the same customer's ID can (and should) appear many times, once per order.
3.Unique values: a primary key's defining requirement, no duplicates. CustomerID in DimCustomer must be unique for the relationship to behave correctly.
4.Cardinality: describes how many times a key value can repeat on each side of the relationship - this is what defines whether a relationship is 1:1, 1:, or *:.
5.Referential integrity: the guarantee that every foreign key value in the fact table actually has a matching row in the dimension table - an order referencing CustomerID = 507 should only exist if a customer with ID 507 actually exists in DimCustomer. Broken referential integrity (an order with no matching customer) shows up as blank or "unknown" values in reports.
6.Active vs. inactive relationships: Power BI allows only one active relationship between two tables at a time. Any additional relationship between the same two tables must be marked inactive, and can only be invoked deliberately inside a DAX measure using USERELATIONSHIP().
I ran into this directly while building the Kenya Crops model: the fact table has both a Planting Date and a Harvest Date, and both logically need to relate to the same Calendar dimension. Power BI let the Planting Date relationship save as active (visible as the solid line to Calendar in Figure 1), but automatically marked the second relationship, to Harvest Date, as inactive - drawn as the dashed line in that same diagram the moment I tried to create it, because a Calendar table can only actively filter a fact table through one date column at a time.
To actually use the inactive relationship, it has to be explicitly invoked inside a measure:
Total Yield by Harvest Date =
CALCULATE(
SUM('Kenya_Crops_Dataset'[Yield]),
USERELATIONSHIP(Calendar[Date], 'Kenya_Crops_Dataset'[Harvest Date])
)
Without USERELATIONSHIP(), any measure placed on a Calendar-based visual would default to filtering by Planting Date only - Harvest Date would simply be ignored by the model unless specifically activated this way.
Why CustomerID is unique in DimCustomer but repeats in FactSales: this is the cardinality relationship in action. DimCustomer describes each customer once - the "one" side. FactSales records every transaction that customer made - the "many" side. The same CustomerID legitimately appears multiple times in FactSales because that customer placed multiple orders, while it can only appear once in DimCustomer because a customer is only described once.
4. Filter Direction
Filter direction controls which way a selection in one table affects another related table.
Single-direction filtering (the Power BI default for most relationships): a filter applied to the "one" side (a dimension) flows down to the "many" side (the fact table), but not the other way around. Selecting "Nairobi" in DimLocation filters FactSales down to only Nairobi's transactions - but filtering FactSales some other way (say, only orders over KSh 5,000) does not, by default, filter which cities show up in DimLocation.
Bidirectional filtering: the filter can travel both directions - a selection in the fact table can also filter back up into a connected dimension.
Example: selecting "Electronics" in DimProduct filters FactSales down to only electronics sales - this is standard single-direction behaviour and is exactly what powered the slicers in my earlier Jumia Excel dashboard (clicking "Excellent" on the Rating Category slicer filtered the connected pie charts the same way a dimension filter flows down to a fact table in Power BI).
Why bidirectional filtering should be used carefully: turning on both-direction filtering can create ambiguous filter paths - situations where Power BI has more than one possible route a filter could travel to reach a table, and can't determine which one should win. This is especially risky in models with multiple relationships between the same tables, or several dimensions connected to more than one fact table. It also adds real query overhead, since the engine now has to evaluate filter propagation in both directions across every calculation. The general guidance is to leave relationships single-direction by default and only switch to bidirectional when a specific, well-understood use case genuinely requires it - not as a default "just in case" setting.
5. Joins in Power Query
A join (called a Merge Query in Power Query) combines two tables based on matching values in a shared column - this happens during data preparation, before the model is even built, unlike a relationship which connects already-loaded tables inside the model.
Consider two example tables:
Customers
| CustomerID | Name |
|---|---|
| 1 | Asha |
| 2 | Brian |
| 3 | Carol |
Orders
| OrderID | CustomerID | Amount |
|---|---|---|
| 101 | 1 | 500 |
| 102 | 1 | 300 |
| 103 | 4 | 750 |
Notice CustomerID 4 appears in Orders but not Customers, and Carol (CustomerID 3) has no orders - these gaps are exactly what each join type handles differently.
Left Outer Join
How it works: keeps every row from the left table (Customers), and matches in any corresponding rows from the right table (Orders) where they exist.
Retained: all rows from the left table; matched rows from the right, or blanks where there's no match.
Example output: Asha's two orders appear twice (once per order); Brian is unmatched - wait, Brian has no order in this data, so Brian would appear once with blank Order fields; Carol appears once with blank Order fields. CustomerID 4's order is dropped, since it's not in the left table.
Right Outer Join
How it works: the mirror image - keeps every row from the right table (Orders), matching in Customer data where it exists.
Retained: all rows from the right table; matched rows from the left, or blanks where there's no match.
Example output: all three orders appear (101, 102, 103); the first two show Asha's name, the third (CustomerID 4) shows a blank Customer name, since no matching customer exists.
Full Outer Join
How it works: keeps every row from both tables, matching where possible and leaving blanks where there's no match on either side.
Retained: everything - matched and unmatched rows from both tables.
Example output: Asha's two orders, Brian with blanks, Carol with blanks, and CustomerID 4's order with a blank customer name - nothing from either table is dropped.
Inner Join
How it works: keeps only rows where a match exists in both tables.
Retained: only the overlapping, matched rows.
Example output: just Asha's two orders (101 and 102) - Brian and Carol are dropped (no orders), and order 103 is dropped (no matching customer).
Left Anti Join
How it works: keeps only rows from the left table that have no match in the right table - effectively the opposite of an inner join.
Retained: unmatched left-table rows only.
Example output: Brian and Carol - customers with no orders at all. Useful for finding "customers who have never ordered anything."
Right Anti Join
How it works: the mirror of a left anti join - keeps only rows from the right table with no match in the left.
Retained: unmatched right-table rows only.
Example output: order 103 - an order that references a customer who doesn't exist in the Customers table. Useful for finding orphaned or data-quality-flagged records, similar to the negative Review values I had to catch and fix in my Jumia Excel dataset.
6. Power Query Joins vs. Power BI Relationships
These solve a similar-sounding problem - combining related data - but they work at completely different stages and in fundamentally different ways.
Does a Power Query merge physically combine data? Yes. A merge in Power Query happens during data loading/transformation, and it produces a genuinely new, combined table (or adds new columns pulled in from the second table) - the two tables' data is physically brought together into one result before the model is even built.
Does creating a relationship combine the tables? No. A relationship leaves both tables completely separate and intact inside the model. It only tells Power BI how to look across from one to the other at query time - conceptually much closer to how XLOOKUP worked in my Excel dashboard, pulling a value across on demand, without ever merging the two sheets into one.
Stage of the workflow:
1.Merges happen in Power Query (the "Get Data" / transformation stage) before the data lands in the model.
2.Relationships are created in Model view, after the tables already exist as separate entities inside the report.
When to choose a merge instead of a relationship: when you need specific columns from Table B pulled directly into Table A as part of a single flattened result - for instance, if a single visual absolutely needs a column that DAX can't easily reach across a relationship, or you're deliberately building a single reporting table for a specific narrow purpose.
How excessive merging affects the model: overusing merges tends to recreate the flat-table problem from Section 1 - data gets duplicated across many wide, overlapping tables, storage and refresh times increase, and you lose the clean, redundancy-free structure a star schema is designed to give you.
Why keep fact and dimension tables separate: separate tables connected by relationships keep the model small, avoid duplicating dimension data across every fact row, let Power BI's engine compress and query efficiently, and make it easy to reuse the same dimension (say, DimDate) across multiple fact tables without rebuilding it each time. Merging everything into one table trades all of that away for a false sense of simplicity.
7. Recommended Power BI Model
For a typical business intelligence project, I would recommend a star schema, with single-direction, one-to-many relationships flowing from each dimension into a central fact table, reserving bidirectional filtering only for specific, well-justified cases.
Justification:
1.Query and report performance: Power BI's VertiPaq engine is purpose-built to compress and query star schemas efficiently. A snowflake schema's extra join hops add measurable overhead at scale; a flat table's redundancy bloats storage and slows refresh.
2.DAX simplicity: with a clean star schema, most measures are simple aggregations (SUM, AVERAGE, DISTINCTCOUNT) that automatically respect whatever filters are applied across any connected dimension, thanks to default filter propagation - no need for complex USERELATIONSHIP() gymnastics or ambiguous-path troubleshooting.
3.Model readability: a star shape is genuinely easier for anyone (including a future version of the analyst who built it) to look at and immediately understand - every dimension is one hop from the fact table, full stop.
4.Scalability: adding a new dimension later (a new DimPromotion, say) is a clean, additive change - connect it directly to the fact table, no restructuring required.
5.Data redundancy: dramatically lower than a flat table, since descriptive attributes live once in each dimension rather than repeating on every transaction row.
6.Maintainability: updating a product's category means updating one row in DimProduct, not hunting through thousands of fact rows.
I would only introduce snowflaking for a specific dimension where there's a clear, demonstrated reason - a genuinely large, deeply hierarchical dimension where normalizing meaningfully reduces redundancy - rather than snowflaking by default. And I would keep relationships single-direction unless a specific reporting requirement (like a genuine many-to-many scenario needing a bridge table) demands otherwise, since the performance and ambiguity costs of bidirectional filtering outweigh the convenience in most everyday reporting needs.
A Note on Data Quality in the Worked Example
Building this model surfaced two real data-quality issues worth naming rather than hiding, in the same spirit as the cleaning work documented in an earlier Excel project I completed on Jumia product data:
1.Missing Harvest Dates: roughly 3% of records had a blank Harvest Date, which had to be filtered out of the Calendar table before it could be used as a relationship key - a dimension table's key column cannot contain blanks.
2.A mismatched category: the DimSeason dimension, built by extracting unique values from the fact table's Season column, ended up with only three values (Long Rains, Short Rains, Unknown), while the fact table itself also contained a fourth value, "Dry Season," not captured in that extraction. Left unresolved, any report slicing by Season would silently drop or misclassify those rows - a reminder that even a mechanically correct relationship can sit on top of an incomplete dimension, and that building a star schema doesn't eliminate the need to also check the data feeding it.
Conclusion
The shift from an Excel-style flat table to a Power BI star schema mirrors a lesson from building the Jumia product dashboard: the shape you organize data into isn't cosmetic, it directly determines how easy, fast, and trustworthy every downstream calculation and chart will be. A star schema with clean, single-direction relationships is the closest thing Power BI has to a default best practice, and understanding why it wins - not just that it does, is what separates rearranging tables from actually modelling data.
Top comments (0)