Beginner's in Power BI often arrive from Excel or SQL and reach for the tools they already know. In Excel that means VLOOKUP to pull columns from one sheet into another; in SQL it means a JOIN that flattens several tables into a single result set. Applied to Power BI, that instinct produces one wide table that holds everything.
Power BI is designed to work differently. Instead of combining tables into one, it keeps them separate and connects them with relationships so that a filter on one table can affect calculations in another. Understanding when to combine tables (a join) and when to relate them (a relationship), and how to arrange them (a schema), is the foundation of a model that is fast, correct, and maintainable.
This guide covers three connected ideas: schemas (how tables are arranged), relationships (how tables are connected in the model), and joins (how tables are combined into one). It assumes no prior modelling experience.
Fact tables and dimension tables
Before arranging tables, you need to classify them. Every analytical model sorts its tables into two kinds.
A fact table records events or measurements one row per sale, per order, per reading. Fact tables are long (many rows), narrow (mostly keys and numbers), and grow over time. You aggregate them: sum the revenue, count the orders, average the quantity.
A dimension table describes the things involved in those events customers, products, dates, stores. Dimension tables are shorter, wider (many descriptive columns), and change slowly. You filter and group by them: revenue by product, orders by month.
| Fact table | Dimension table | |
|---|---|---|
| Grain | One event | One entity |
| Row count | High, growing | Low, stable |
| Typical columns | Keys and numeric measures | Descriptive attributes |
| Used to | Aggregate | Filter and group |
| Examples |
Sales, Orders
|
Customer, Product, Date
|
The question to ask of each table is whether you measure it or describe with it. Sales is measured. Product describes what was sold. That classification determines where each table sits in the schema.
The star schema
A star schema places one fact table at the centre, with dimension tables connected to it, each by a single key. Drawn out, the dimensions surround the fact like the points of a star.
flowchart TD
D1[Dim Date] --> F[(Fact Sales)]
D2[Dim Customer] --> F
D3[Dim Product] --> F
D4[Dim Store] --> F
Filters flow from the dimensions into the fact. Selecting a product in the Product table filters the Sales table to that product's rows; selecting a month in the Date table filters Sales to that month. This one-directional flow is the arrangement Power BI's engine (VertiPaq) and its formula language (DAX) are built to handle.
The star schema is the recommended default in Power BI for concrete reasons: the engine compresses and scans a central fact with flat dimensions efficiently, DAX filter behaviour is predictable when filters flow dimension → fact, there are fewer relationships to traverse than in the alternatives, and analysts find fields where they expect them. When writing DAX against a model feels unexpectedly difficult, the model shape is the usual cause rather than the formula.
The snowflake, and why to avoid it
A snowflake schema normalizes a dimension into sub-dimensions: instead of one Product table, you have Product → Subcategory → Category as three separate linked tables.
flowchart LR
Cat[Dim Category] --> Sub[Dim Subcategory] --> Prod[Dim Product] --> F[(Fact Sales)]
This mirrors how a normalized source database stores the data, but in Power BI it adds relationships to traverse and complicates both DAX and the field list. The standard advice is to collapse a snowflake back into a star by merging the sub-dimensions into one flat Product table which is a join, covered below.
Relationships
A relationship links two tables on a shared key so that a filter on one propagates to the other. Relationships are what let a slicer on Product change a total stored in Sales, without the two tables ever being combined.
The "one" side and the "many" side
Relationships are described by their cardinality how many rows on each side can share a key value.
| Type | Meaning | Example |
|---|---|---|
| One-to-many (1:*) | One row on the "one" side matches many rows on the "many" side | One Product → many Sales rows |
| Many-to-one (*:1) | The same relationship seen from the other end | Many Sales → one Product
|
| One-to-one (1:1) | Each row matches at most one row on the other side |
Employee ↔ EmployeeDetails
|
| Many-to-many (:) | Both sides can have repeated key values |
Accounts ↔ Customers via joint accounts |
One-to-many is the backbone of a star schema. The dimension is the "one" side each product appears once in Product and the fact is the "many" side, because a product appears in many sales. For this to work, the key on the dimension side must be unique; a duplicate product key makes the relationship invalid.
Cross-filter direction
Cross-filter direction controls which way filters travel across a relationship.
Single direction means filters flow one way only, from the "one" side to the "many" side (dimension → fact). This is the default and the correct choice for almost every relationship in a star schema.
Both (bidirectional) direction means filters flow in both directions. It is occasionally required for example, to filter a dimension down to only the values that actually appear in the fact but it introduces ambiguity, can create circular filter paths, and slows queries. Use it only for a specific, identified reason. Bidirectional relationships are a common source of incorrect totals and "ambiguous path" errors in larger models.
flowchart LR
Dim[Dimension] -->|Single: filters flow down| Fact[(Fact)]
Dim2[Dimension] <-->|Both: filters flow both ways| Fact2[(Fact)]
Active and inactive relationships
Two tables can have more than one relationship, but only one can be active at a time. The active relationship (a solid line in the model diagram) carries filters automatically; additional relationships are inactive (a dashed line) until a measure activates one with the USERELATIONSHIP function.
The common case is a Sales table with OrderDate, ShipDate, and DueDate all pointing at one Date table. One relationship is active say OrderDate and the others stay inactive until a specific measure needs them:
Sales by Ship Date =
CALCULATE (
[Total Sales],
USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)
Joins (Merge) versus relationships
This is the distinction that most often confuses people coming from SQL or Excel, and it is the reason the "one big flat table" instinct leads to trouble.
A join in Power BI happens in the Power Query Editor through the Merge operation. It matches rows from two tables on a key and brings columns across to form a single wider table. Merge is the equivalent of a SQL JOIN.
A relationship happens in the model (the Model view). It leaves the two tables separate and lets filters propagate between them when a visual is rendered.
The two solve the same underlying problem using data from more than one table together in different places and with different results:
| Join (Merge in Power Query) | Relationship (Model view) | |
|---|---|---|
| Where | Power Query, before load | The model, after load |
| Result | One combined table | Two separate tables, connected |
| When it runs | At refresh | At query time |
| SQL analogy | JOIN |
No direct equivalent |
| Use it to | Build or flatten a single table | Connect a fact to its dimensions |
The guidance for a beginner is straightforward: connect a fact table to its dimension tables with relationships, not merges. Merging everything into one flat table works, but it discards the benefits of the star schema larger file size from repeated dimension values, dimensions that cannot be reused across multiple facts, and more complex DAX.
Use a merge for the narrower job of shaping a single table before it enters the model: collapsing a snowflake's sub-dimensions into one flat Product table, bringing a region column from a lookup into a dimension, or building a composite key. In short, a merge prepares a table; a relationship connects tables.
Join types in a Merge
When you do merge, Power Query offers the standard join kinds. The two you will use most are the first two.
- Left Outer - keep every row from the first table, add matching columns from the second. The most common choice, and the default.
- Inner - keep only rows that match in both tables.
- Right Outer / Full Outer - keep all rows from the second table, or from both.
- Left/Right Anti - keep only rows in one table that have no match in the other. Useful for finding orphaned records, such as sales whose product ID is missing from the product table.
Cardinality and why it affects correctness
"Cardinality" appears twice in Power BI, and both meanings matter to a beginner.
Relationship cardinality is the 1:, *:1, 1:1, or *: label on a relationship. Power BI detects it automatically when the relationship is created, but it can detect it wrongly from a sample of data, and the wrong label changes results. If a genuine one-to-many relationship is labelled one-to-one because an early sample looked unique, later duplicate keys are silently dropped from filter propagation and totals are understated with no error shown. Confirm that each relationship's cardinality matches the real data, and keep dimension keys unique.
Column cardinality is the number of distinct values in a column. It is the largest single driver of model size, because the engine compresses low-cardinality columns (a Country column with about 200 values) far better than high-cardinality ones (a transaction timestamp to the second). For a first model, two habits help: remove high-cardinality columns you do not analyse, and split a date-time column into separate Date and Time columns so each compresses well.
Common beginner mistakes
- One flat table for everything. Merging all data into a single table is the Excel and SQL habit. It functions, but forgoes the star schema's performance and reuse. Separate facts from dimensions and connect them with relationships.
- Bidirectional filtering by default. Setting cross-filter direction to Both everywhere causes ambiguous filter paths and wrong totals. Keep it Single unless a specific need is identified.
- A non-unique key on the "one" side. A dimension key that repeats makes the relationship invalid, or forces a many-to-many that behaves unexpectedly. Deduplicate the dimension.
- Wrong-grain merge causing fan-out. Merging on a key that is not unique in the second table multiplies rows and inflates totals. Confirm the join grain before merging.
- Mismatched key data types. A relationship cannot form if one key is text and the other is a whole number. Align the types in Power Query first.
Summary
- Classify every table as a fact (measured) or a dimension (described with).
- Arrange them as a star schema: one fact in the centre, dimensions around it, filters flowing dimension → fact.
- Connect fact and dimensions with relationships, usually one-to-many with single cross-filter direction.
- Use a join (Merge) to shape or flatten an individual table, not to replace the model. A merge combines tables into one; a relationship keeps them separate and connected.
- Confirm relationship cardinality matches the data, and keep dimension keys unique, so totals stay correct.
Getting these fundamentals right removes most of the problems that beginners otherwise try to fix later with complicated DAX. A correct model structure does most of the work that difficult formulas are often written to compensate for.
Top comments (0)