What does a relationship between two tables in the model actually do?
explanation: A relationship does not move data. It is a pipe: a selection in one table narrows the rows in the other.
modeling data
From the engine under the hood to a working star schema: VertiPaq, relationships, Star Schema and cardinality — on real AdventureWorks data.
36Topics in the lesson
40Questions about relationships
12Tasks in the exercise
121KRows in the data
opening
How the lesson works
Three minutes of reading that will save you searching: how to navigate, where the questions are, and what's saved. Then the lesson content, the map of parts, and preparation for practice.
01
Navigating between topics
the buttons Previous Fnext fixed at the top of the screen. You can also use the keyboard, swipe on your phone, or jump from the topic list.
02
Questions and practice
the button for the test at the top of the screen always leads to the first questions page: self-test, homework exercise and multiple-choice quiz.
03
Progress is saved
The topic where you stopped and the topics you have read are saved only in your browser. You can close and come back, and reset from the topics list.
The course's four lessons
The full track. You can jump to any lesson with a click.
Lesson map — ten chapters
from the engine under the hood, through the reason relationships are needed, to a star schema working on real data.
| chapter | what we'll learn |
|---|---|
| I · The engine | VertiPaq: columnar storage, compression, and why a good model matters to it |
| II · Why relationships | What happens without a relationship, a relationship as a filter pipe, and PK and FK keys |
| III · Facts and dimensions | Fact vs Dimension, our seven tables, and a degenerate dimension |
| IV · Star vs Snowflake | Both structures, head-to-head comparison, and why Star wins |
| V · The relationship mechanism | Cardinality, filter direction, active vs inactive relationship |
| VI · Bidirectional relationship | The full story: from one slicer all the way to the customer list, step by step |
| VII · Quick Guide | Deleting relationships in four steps, with screenshots |
| VIII · Summary | Eight lessons and a 40-question quiz on relationships |
| IX · Practice | 12 hands-on tasks on AdventureWorks |
| X · What's next | Resources and next steps |
Preparation for practice
The hands-on exercise comes at the end of the lesson (topic 29). It is worth downloading the data and opening Power BI Desktop right now.
practice
AdventureWorks_Sales.xlsx — seven sheets
Sales with 121,253 rows and six accompanying tables. Total sales in the file: $109,809,274.20. Every number in the lesson is verified against this file.
Chapter I · The VertiPaq engine
What is VertiPaq
Tabular storage engine In-Memory column-based — the core on which Power BI's data model is built.
1
In-Memory
The engine runs entirely in RAM and doesn't access disk during the calculation.
2
Column-based
Stores the data by columns, unlike SQL which stores by rows — enabling efficient compression and retrieval.
3
Core technology
responsible for storage, compression and data retrieval — and it's the same engine that also runs Analysis Services.
Chapter I · The VertiPaq engine
What VertiPaq actually does
Four roles, all on data compressed in memory.
1
Data compression
Identifies repeating values and builds internal dictionaries. Compression ratios of 1:10 are routine.
2
Columnar storage
measure on Sales Amount touches only one column — the other 14 columns aren't read at all.
3
DAX processing
Calculates in real time on the compressed data and returns a result to the visual.
4
Managing reciprocal relationships
Translates a filter from the dimension table to the fact table — and that's exactly the topic of the lesson.
Chapter I · The VertiPaq engine
Why an engine like this is even needed
1
Data volumes
Millions of rows on a regular laptop, without draining memory.
2
An interactive experience
Clicking a filter returns a result immediately, with no waiting.
3
Quick calculations
Efficient scanning while computing measures — critical in complex models.
The point that will accompany the whole lesson: The engine is fast, but only if the model is built correctly. A flat or branched model cancels out the advantage.
Chapter II · Why relationships are needed
What happens right after loading
- Loaded seven tables. Each one came in as a separate "island".
- Power BI doesn't know thatSales[ProductKey] related toProduct[ProductKey].
- At this stage you can cut within each table — but not between tables.
- The modeling task: build the bridges that connect the islands.
Power BI does create some relationships automatically on load — and they're not always accurate. So you go over them manually, one by one.
Chapter II · Why relationships are needed
The test: what happens when there's no relationship
One visual that proves the whole point: Sales Amount by Category, with no relationship between Product and Sales.
| Category | Without a relationship — broken | With a relationship — valid |
|---|---|---|
| Bikes | $109,809,274.20 | $94,620,526.21 |
| Components | $109,809,274.20 | $11,799,076.66 |
| Clothing | $109,809,274.20 | $2,117,613.45 |
| Accessories | $109,809,274.20 | $1,272,057.89 |
The filter from Category doesn't find a way to Sales, so SUM(Sales[Sales Amount]) runs over the whole table in every row. The moment the relationship is built — every category gets its own sum.
Chapter II · Why relationships are needed
Another example: sales by year
| Fiscal Year | regardless | with a relationship |
|---|---|---|
| FY2018 | $109,809,274.20 | $23,860,891.17 |
| FY2019 | $109,809,274.20 | $34,070,108.50 |
| FY2020 | $109,809,274.20 | $51,878,274.54 |
Without the relationship the chart comes out completely flat — as if we sold the same thing every year. With the relationship Date[DateKey] → Sales[OrderDateKey] A 2.2x growth over three years is revealed. That's a whole story that disappears without one relationship.
Chapter II · Why relationships are needed
Delete the relationships — and the star falls apart
- Delete all the relationships in Model view — and the star goes back to being seven islands.
- There's no pipe through which a filter can flow from the dimension to Sales: any filtering is simply ignored.
- Every visual in the report shows the same number — $109,809,274.20 — no matter what you select.
- The reason: the measure runs over the whole table; without a relationship there's nothing to narrow the Filter Context.
Without relationships there's no model — just tables that don't talk to each other. The relationship is what turns data into a report.
Chapter II · Why relationships are needed
A relationship = a pipe through which the filter flows
- A relationship connects a column in one table to a matching column in another.
- It doesn't copy data — it opens a pipe through which filters flow.
- when choosing Category = Bikes, the filter flows through the pipe and narrows Sales.
- The natural flow direction: from the dimension (the descriptor) to the Fact (the events).
- Every relationship has three properties: cardinality, directionality, and active or inactive.
Chapter II · Why relationships are needed
The keys that hold the relationship
| Primary Key — the 1 side | Foreign Key — the * side | |
|---|---|---|
| Where does it sit | In the dimension table | In the Fact table |
| uniqueness | One value = exactly one row | repeats itself — a product sold thousands of times |
| in our data | Product[ProductKey] — 397 values across 397 rows | Sales[ProductKey] — 121,253 rows |
| limitations | No NULLs allowed and no duplicates allowed | Every value must match a PK that exists in the dimension |
A quick check in Power Query: Column profile On the key column. If Distinct = the number of rows and no blanks — that's a valid PK.
Chapter III · Facts and Dimensions
Fact vs. Dimension — how to identify
| Fact — facts | Dimension — dimensions | |
|---|---|---|
| what it records | Events: sale, order, return | Entities: product, customer, date, region |
| size | Tons of rows, growing every day | Few rows, stable |
| content | Numbers to summarize: quantity, amount, cost | Text and categories for slicing and filtering |
| keys | Foreign keys to every dimension | Unique primary key |
| The identification question | "How much? By how much?" | "Who? What? When? Where?" |
Rule of thumb: Numbers that get summed ← Fact. Descriptions that filter ← Dimension.
Chapter III · Facts and Dimensions
The seven tables of AdventureWorks
121,253Rows in Sales
$109.8MTotal sales
397products
18,485customers
31,455orders
| table | rows | type | role |
|---|---|---|---|
| Sales | 121,253 | Fact | Each row = an item sold |
| Sales Order | 121,253 | Degenerate dimension | Channel and order numbers |
| Customer | 18,485 | Dim | End customers |
| Reseller | 702 | Dim | marketers |
| Product | 397 | Dim | including Category and Subcategory as columns |
| Date | 1,461 | Dim | 1.7.2017 to 30.6.2021 |
| Sales Territory | 11 | Dim | Sales regions |
One Fact table records what happened; the other six tables describe who's involved.
Chapter III · Facts and Dimensions
How is it classified in our data
| Sales — the Fact | The six dimensions | |
|---|---|---|
| seed | SalesOrderLineKey is unique per row | One primary key per entity |
| Key columns | Seven foreign keys — it's always the * side | PK unique — always the 1 side |
| measures | Sales Amount · Order Quantity · Total Product Cost | No measures, only descriptions |
| size | 121,253 rows and growing | 11 to 18,485 rows, stable |
Sales has 15 columns, seven of them keys. This is the classic signature of a fact table in a star schema.
Chapter III · Facts and Dimensions
Sales Order — the special case: a degenerate dimension
121,253 rows — exactly like Sales. A 1:1 relationship, no measures.
?
Why not Fact
It has no measure to summarize — only Channel, order number (31,455 distinct) and line number in the order.
?
Why not a regular dimension
A normal dimension is 1:N with few rows. Here the relationship is 1:1 with the exact same grain as the Fact.
✓
So what is it
Degenerate dimension (Degenerate Dimension): order attributes that were pulled out into a side table. The main use: segmentation by Channel.
The breakdown it enables: Reseller $80,450,596.98 vs. Internet $29,358,677.22 — 73% of the money comes from the wholesale channel.
Chapter III · Facts and Dimensions
1:1 vs 1:N relationship
| 1:N — Product vs Sales | 1:1 — Sales Order vs. Sales | |
|---|---|---|
| the meaning | One product repeats across thousands of sales | Each row matches exactly one row |
| the numbers | 397 ← 121,253 | 121,253 ↔ 121,253 |
| When it appears | The default between a dimension and a Fact | When both tables describe the same event |
Both types are valid. In 1:1, the match is perfect row-to-row, and that's a sign the tables describe the same thing. In 1:N, the dimension side is unique and the Fact side repeats.
Chapter IV · Star vs. Snowflake
Why not one giant table
- duplicate: The product name, category and color used to repeat across all 121,253 rows.
- maintenance: Renaming a category = updating tens of thousands of rows.
- analysis: You can't see products that weren't sold — they simply don't exist in the table.
in our file 47 out of 397 products were never sold, and 66 out of 702 marketers with no sales. In a single flat table this information disappears.
The solution: split by responsibility. One table records what happened, other tables describe who’s involved. Less memory, more speed, and full analysis.
Chapter IV · Star vs. Snowflake
Star Schema — a star with Fact at the center
Every dimension connects directly to Fact — one hop from every filter to the data. This is the structure Power BI is built for: flat, fast in VertiPaq, and easy to filter.
A clean diagram: dimensions, direction arrows, and cardinality
Chapter IV · Star vs. Snowflake
The star — sketching the model
A clean diagram of the model: the fact table in the center, the dimensions around it, and arrows showing where the filter flows. Click a table to highlight its relationship, and switch between single- and bi-directional to see the difference.
Chapter IV · Star vs. Snowflake
Snowflake — dimensions split into a chain
- Same Fact at the center — but the dimensions are normalized (split) into tables.
- instead Dim Product One wide table: Category ← Subcategory ← Product.
- Every filter on Category passes through a chain of three relationships on the way to Fact.
- Comes from the world of data warehouses (DWH), where normalization saves disk space.
- The name: the diagram branches out like a snowflake.
Chapter IV · Star vs. Snowflake
neck and neck
| criterion | Star Schema | Snowflake |
|---|---|---|
| Dimension structure | One wide table per topic | Split into a chain of tables |
| Number of relationships | Few — one direct relationship | A lot — a chain of relationships |
| Performance in Power BI | Fast — one jump | Slower — how many jumps |
| Fetching a value from the dimension | Direct — one jump | Requires passing through several tables |
| Memory savings | VertiPaq compresses excellently | Normalization doesn't help much |
| source | The world of BI and analytics | The world of DWH and normalization |
Chapter IV · Star vs. Snowflake
So why does Star win
- In a traditional DWH, normalization saves disk space — a real advantage.
- But VertiPaq compresses columns brilliantly, and the compression cancels out the savings.
- What's left of Snowflake is only the drawbacks: more relationships, slower performance, complex DAX.
The practical rule: In Power Query you merge the chain of dimensions into one wide dimension table. In our file this is already done — Category and Subcategory sit as columns inside Product.
Chapter V · The relationship mechanism
Cardinality
- Cardinality = the numeric ratio between two tables in a relationship.
- One-to-Many (1:N) — a unique dimension filters a repeating Fact. That's the healthy state.
- Example: one product (1) sold across thousands of sales rows (N).
- Many-to-Many (N:M) — both tables have duplicates. A warning sign.
- The marker on the line: 1 on the dimension side, * On the Fact side.
If Power BI suggests N:M — stop. In most cases a duplicate got into the dimension table, and that's what needs fixing.
Chapter V · The relationship mechanism
Filter directionality
| Single — default | Both — bidirectional | |
|---|---|---|
| The flow direction | From the 1 side to the * side only | Also from Fact back to dimension |
| behavior | Predictable, fast, safe | Can distort results with indirect filtering |
| performance | easy to prevent | heavier |
| Additional risk | none | Ambiguous filter paths (Ambiguity) |
The analogy: Single = one-way street, traffic is predictable. Both = a roundabout with no traffic signs — sometimes it works out, and under load it crashes. In 99% of cases you leave it as Single.
Chapter V · The relationship mechanism
Active vs. inactive relationship
- Sales has three date columns: OrderDateKey, DueDateKey, ShipDateKey.
- They all want to connect to Date — but only One active relationship Between a pair of tables.
- The active relationship (solid line): OrderDateKey — the default for all calculations.
- The inactive relationships (dashed line): lie ready and are activated selectively in DAX.
How do you activate an inactive relationship? Point-in-time, within a specific calculation — and only for that calculation. The active relationship and the rest of the report are unaffected. We'll learn the exact syntax in the DAX lesson; at this stage the important thing is understanding the mechanism: the relationship exists, lying ready, and gets activated on demand.
Chapter V · The relationship mechanism
Why an inactive relationship is actually good
?
Why only one is active
Two active relationships at the same time = means ambiguity. Power BI wouldn't know which date to filter by, so it forbids this.
✓
What we gained
One clean date table serves all three dates, instead of duplicating it three times.
!
Note the empties
In AdventureWorks there are 2,113 rows with no ship date ($1,281,073.41) — on the ShipDateKey relationship they'll fall into the blank row.
Chapter VI · Bidirectional relationships in depth
The rule: filtering has a direction
Filtering has a direction, like a stream flowing downriver. This chapter breaks the idea down step by step on one question: Which customers bought sunglasses?
The example is based on the Contoso Retail Star model · all numbers were computed from the data
Date1,096
Product400 products
Customer2,000 customers
Store25
Employee118
Sales52,000 rows
↓ all five arrows go into Sales — and no arrow leaves it
Every line in the model = a one-way pipe
Filtering always flows from the dimension to the fact, in the direction of the arrow. Pick a year in Date? The arrow pushes the filter down to Sales. Pick a category in Product? Same thing. Never against the flow.
and that's enough for 90% of the questions
"How much did we sell", "When", "Where", "Who sold" — for all of them the Single default is enough. But what happens when the answer sits in one dimension and the filter is in another?
Chapter VI · Bidirectional relationships in depth
Starting point — three tables, two pipelines
Three tables, two pipes. The yellow columns are the keys — filtering flows through them.
400Product · Products
←
Sales52,000 rows
→
2,000Customer · Customers
Product dimension
| ProductKey | ProductName | Subcategory |
|---|---|---|
| 214 | Aviator Classic | Sunglasses |
| 215 | Sport Polarized | Sunglasses |
| 87 | TrekPro Red Sock | Socks |
| 132 | Alpine Jacket | Jackets |
396 more rows ⋮
Sales fact
| OrderID | ProductKey | CustomerKey | Amount |
|---|---|---|---|
| 10442 | 214 | 551 | ₪240 |
| 10871 | 215 | 1203 | ₪310 |
| 10902 | 87 | 551 | ₪35 |
| 11054 | 132 | 890 | ₪520 |
51,996 more rows ⋮
Customer dimension
| CustomerKey | Name | City |
|---|---|---|
| 551 | Mia Dubois | Paris |
| 890 | Dana Mor | Haifa |
| 1203 | Liam Carter | London |
1,997 more rows ⋮
Every sales row has ProductKey ("what was sold") andCustomerKey ("who bought"). Power BI filters by matching keys — and only in the direction of the arrow.
Chapter VI · Bidirectional relationships in depth
Four steps: from Sunglasses to 662 customers
The same story in four steps. Click a step to see what happens to the numbers in each table.
SunglassesSlicer · Subcategory
←
13Product · out of 400
←
52,000Sales · rows
→
2,000Customer · Customers
What happens in the tables themselves
Product — 13 remaining
| ProductKey | ProductName | Subcategory |
|---|---|---|
| 214 | Aviator Classic | Sunglasses |
| 215 | Sport Polarized | Sunglasses |
| 216 | Urban Retro | Sunglasses |
| 87 | TrekPro Red Sock | Socks |
| 132 | Alpine Jacket | Jackets |
13 green rows remain ⋮
Sales — 813 remaining
| OrderID | ProductKey | CustomerKey |
|---|---|---|
| 10442 | 214 | 551 |
| 10871 | 215 | 1203 |
| 11433 | 216 | 551 |
| 10902 | 87 | 551 |
| 11510 | 301 | 1203 |
813 green rows remain ⋮
Customer — 662 after Both
| CustomerKey | Name |
|---|---|
| 551 | Mia Dubois |
| 890 | Dana Mor |
| 1203 | Liam Carter |
662 green rows remain ⋮
Chapter VI · Bidirectional relationships in depth
Why not just a slicer on Customer
"Why not just put Customer in the slicer too?" Because a slicer is what you we know — and the list you're looking for is exactly what you no know.
Slicer = input
On Product it's easy: you know you want "sunglasses" — you click and select. But what would you pick in the Customer slicer? You don't know which of the 2,000 customers bought sunglasses — that's exactly the answer you're looking for.
The list = the output
The manager says: "Pull a list of everyone who bought sunglasses, for an SMS campaign". The list is the result, not the selection. Both lets the 813 sales rows tell Customer who appears in them — and the list narrows down to 662 on its own.
and the opposite case — the Both crosses a side
\"What did Mia Dubois buy?\" — now we know the customer and are looking for products. Same idea, opposite direction.
Mia DuboisSlicer · Customer
←
25Sales · its rows
⇄
25Product · what she bought
the rule: What you know ← goes to the slicer. What you're looking for ← is where the filtering needs to reach. And if along the way it has to climb from the fact table to a dimension — for that, and only that, Both.
Chapter VI · Bidirectional relationships in depth
In the matrix: before and after, and when
The same matrix, the same slicer. The only difference: the arrow direction on the Sales–Customer relationship.
Matrix: rows = customer name · values = customer count · slicer = Subcategory: Sunglasses
Before — Single
| Customer Name | customers |
|---|---|
| Alice Werner | 1 |
| Dana Mor | 1 |
| Liam Carter | 1 |
| Mia Dubois | 1 |
| Noah Fischer | 1 |
Total 2,000 — every customer that exists in the table
After — Both
| Customer Name | customers |
|---|---|
| Amelia Novak | 1 |
| Liam Carter | 1 |
| Mia Dubois | 1 |
| Omar Haddad | 1 |
| Sofia Rinaldi | 1 |
Total 662 — only those who appear in the 813 sales rows
Why does it sometimes "work" even without Both? If the values in the matrix come from Sales (say, sum of sales), empty rows are automatically hidden and everything looks fine. But the moment the matrix relies on the dimension itself — a list of names, a customer count, fields from Customer only — without Both you'll get all 2,000 rows.
When yes and when no
✓ Turn on Both when…
The question is "which / who from the dimension?" and the filter sits in a different dimension: which customers bought X, which products did customer Y buy, which employees sold in category Z. The telltale sign: a list that refuses to shrink — 2,000 instead of 662.
✗ Don't turn on Both when…
The question is "how much?" — sums, quantities, trends. Single is always enough there. And don't turn on "just in case" for everything: every extra bi-directional pipe complicates the model and slows it down.
The sentence to remember: Filtering always flows with the arrow, from the dimension to the fact. Both doesn't change the relationship — it just adds a second arrowhead, so the fact can filter the dimension back. You open it only for a "which / who" question, and close it when you're done.
Chapter VII · Quick Guide
Deleting relationships in four steps
to demonstrate for yourself what happens without relationships — or to clean up a model that got filled with incorrect automatic relationships.
The warning in the dialog — "visuals using these tables may break" — is exactly the demonstration we want.
Chapter VII · Quick Guide
Tip: the relationships won't come back on their own
Before reloading the data:
File → Options and settings → Options
→ Current File → Data Load
→ [ ] Autodetect new relationships after data is loadedWhen the option is off, Power BI won't automatically recreate relationships — the demo will stay clean, and every relationship in the model is created consciously.
Chapter VIII · Summary and self-check
What we learned — eight points
01
VertiPaq
A columnar in-memory engine. It compresses, pulls only what's needed, and runs DAX on the compressed data.
02
Without a relationship there's no model
Every visual will show the grand total, $109,809,274.20, in every row.
03
Relationship = filter pipe
It doesn't copy data; it lets filtering flow from the dimension to the Fact.
04
PK and FK
Unique on the dimension side, repeating on the Fact side. Distinct = number of rows, that's the check.
05
Fact vs Dim
Numbers that sum versus descriptions that filter. Sales Order is a degenerate dimension in a 1:1 relationship.
06
Star wins
One hop from each dimension. Snowflake's normalization doesn't help because VertiPaq already compresses.
07
1:N and Single
The healthy default. N:M and Both are red flags that need checking.
08
Inactive relationship
One date table serves three dates: one active, the rest waiting and activated as needed.
40 questions, all about relationships in the model — direction, cardinality, active and inactive
Chapter VIII · Summary and self-check
Quiz — 40 questions on relationships
One question at a time, four options, immediate feedback with an explanation. There is no passing score — the goal is to identify what is worth reviewing.
You loaded seven tables and defined no relationships. What's the state of the model?
explanation: without a relationship, each table lives on its own. Power BI does guess some relationships on load, but not always correctly.
In a sales-by-category visual, with no relationship between the products table and the sales table — what would show?
explanation: The filter can't find a path to the fact table, so every row gets the full sum of the entire table.
What's the natural direction of filter flow in a star schema?
explanation: The dimension describes, the fact records. Filtering flows down from the descriptor to the events — that is the arrow direction.
In Model view, what does the arrow on the relationship line signify?
explanation: The arrow is the filter direction. Filtering against the direction of the arrow simply doesn't happen.
A model where every dimension connects directly to the fact table — how many "hops" does a filter take from the dimension to the data?
explanation: This is the advantage of a star schema: one direct relationship between each dimension and the fact.
In a model where the dimensions are split into a chain (category ← subcategory ← product), what happens to a filter on the category?
explanation: Every relationship in the chain is another hop — more work for every query.
You deleted all the relationships in the model. What happens to the visuals in the report?
explanation: This is exactly why the system warns before deleting: visuals that rely on the relationships "break".
Where does the primary key sit in a valid relationship?
explanation: The 1-side is the dimension: one value = exactly one row, no duplicates and no blanks.
What characterizes the foreign key in the fact table?
explanation: The * side is the fact. The same key value appears in many event rows.
Which check confirms that a column is fit to serve as a primary key?
explanation: If there's a duplicate or a blank — it's not a primary key, and Power BI will suggest a problematic cardinality.
The products table has 397 rows and the sales table has 121,253. Which cardinality will result?
explanation: One product appears in thousands of sales rows. The dimension is always on the 1 side.
What do the symbols 1 and * on the relationship line indicate?
explanation: The 1 on the dimension side, the * on the fact side. That's the relationship's cardinality.
Two tables with the same row count and the same grain — for example a sales row and an order row. What relationship will be created?
explanation: A 1:1 relationship is perfectly valid, and it's a sign that the tables describe the exact same event.
Power BI suggests a many-to-many cardinality between dimension and fact. What's the first step?
explanation: Many-to-many between a dimension and a fact is almost always a symptom of duplication in the dimension, not a solution.
Why is a many-to-many relationship considered riskier than one-to-many?
explanation: When both sides have duplicates, there's no single way to match row to row — and the result is surprising.
A sales table with 121,253 rows is linked to a customers table with 18,485 rows. Where does the * sit?
explanation: The repeating side is always the fact — every customer appears in many sales rows.
The key column in the dimension is a number, and in the fact table the same column was loaded as text. What will happen?
explanation: A relationship is built between columns of the same type. A mismatch prevents the relationship from being created in the first place.
What's the default cross-filter direction for a new relationship?
explanation: One-directional is predictable, fast, and safe, and suits the vast majority of cases.
You selected a category in the slicer, and the matrix showing customer names didn't shrink at all. What's the likely reason?
explanation: This is normal behavior for a single-direction relationship: filtering flows only with the arrow.
What exactly does making a relationship bidirectional change?
explanation: The cardinality stays as it was. What changed is only the direction of the flow.
In which business question would you need bidirectional filtering?
explanation: "which / who" is the sign: the answer sits in one dimension and the filter in another.
In the sunglasses example: a slicer left 13 products, which filtered 813 sales rows. What will the customer slicer show with single-direction filtering?
explanation: The filtering stopped at the sales table. The customer list didn't shrink at all.
And in the same example, what will happen after making the relationship between sales and customers bidirectional?
explanation: The filtered sales rows now filter the customer dimension back.
What's the risk of turning on bi-directional filtering on several relationships at once?
explanation: Multiple paths is ambiguity: the same selection can lead to different results.
How does bidirectional filtering affect the model's performance?
explanation: The reverse flow costs calculation time, especially when the dimension is large.
A matrix showing sum of sales by customer looks fine even without bidirectional filtering. Why?
explanation: The moment the matrix relies only on columns from the dimension — for example a list of names — the problem shows up.
What is the practical rule regarding bidirectional filtering?
explanation: This is a targeted tool for a "which / who" question, not a sweeping definition for the model.
What's true about the relationship between direction and cardinality?
explanation: Cardinality describes the row ratio, directionality describes where the filter flows.
How many active relationships are allowed between the same pair of tables?
explanation: Two active at the same time would create ambiguity, so the system forbids it.
How is an inactive relationship identified in model view?
explanation: Solid line = active, dashed line = inactive. The relationship exists and is waiting.
A sales table has three date columns: order, due, and shipment. What's the correct solution?
explanation: One clean date table serves all three dates, without duplicating it.
What is the role of an inactive relationship?
explanation: The active relationship continues to serve the rest of the report.
Which of the dates in the sales table is appropriate to leave as the active relationship?
explanation: The order date is the natural default for most analyses, so it stays the active one.
You tried to create a second relationship between the same two tables. What happens?
explanation: The system allows creating it, but marks it as inactive to prevent ambiguity.
2,113 sales rows are empty in the shipping date column, and the relationship is built on this column. What will happen?
explanation: An empty value on the fact side finds no match in the dimension, so an empty row is created — and that's important information.
The regions table has a row that doesn't appear in any sales row. What will be shown for it?
explanation: A dimension row without fact rows is perfectly valid, and sometimes it's exactly the insight.
What is the Manage relationships window used for?
explanation: It shows, for each relationship, the tables, columns, cardinality, and status.
Why is it worth turning off automatic relationship detection before reloading?
explanation: Automatic detection is convenient at first, but it guesses — and not always correctly.
Which behavior indicates you should check the relationships in the model?
explanation: This is the classic signature of a model with no relationships — the filter doesn't reach the fact table.
You haven't answered questions yet — 0 out of 40
Now for the practical part: Power BI Desktop and the AdventureWorks file
Chapter IX · Hands-on Practice
Homework — 12 tasks with solutions
Build the model from scratch on AdventureWorks: delete the automatic relationships, see what breaks, and connect them yourself — with numeric verification at every step.
Before we begin
AdventureWorks_Sales.xlsx
Seven sheets. Every number in the solutions was verified against this file — if you got something different, there's a problem in the model.
Progress tracking
0 out of 5
1 Loading and checking rows
Get Data ← Excel ← AdventureWorks_Sales.xlsx ← select all seven sheets ← Load.
| step | what to do |
|---|---|
| 1 | Check every table in Data view and check the row count at the bottom |
| 2 | Confirm: Sales 121,253 · Sales Order 121,253 · Customer 18,485 · Reseller 702 · Product 397 · Date 1,461 · Sales Territory 11 |
| table | rows | role |
|---|---|---|
| Sales | 121,253 | Fact — every row is an item sold |
| Sales Order | 121,253 | Degenerate dimension — Channel and order numbers |
| Customer | 18,485 | dimension |
| Reseller | 702 | dimension |
| Product | 397 | Dimension — includes Category and Subcategory |
| Date | 1,461 | Dimension — 1.7.2017 to 30.6.2021 |
| Sales Territory | 11 | dimension |
✓ Total sales in the file: $109,809,274.20 · 274,776 units · 31,455 orders.
2 Deleting the automatic relationships
Home ← Manage relationships ← the checkbox in the header ← Delete ← confirm.
| step | what to do |
|---|---|
| 1 | File ← Options ← Current File ← Data Load — turn off Autodetect new relationships after data is loaded |
| 2 | Delete all the relationships and confirm in Model view that seven disconnected islands remain |
✓ Without turning off Autodetect, the relationships will come back on their own at the next refresh, and the demo will break.
3 The test: a visual with no relationship
A table with Product[Category] and Sales[Sales Amount].
| step | what to do |
|---|---|
| 1 | Drag Category to rows and Sales Amount to values |
| Category | what will be shown |
|---|---|
| Bikes | $109,809,274.20 |
| Components | $109,809,274.20 |
| Clothing | $109,809,274.20 |
| Accessories | $109,809,274.20 |
✓ Every category gets the grand total — the filter has no path to Sales.
4 The first relationship — Product
Model view ← drag Product[ProductKey] onto Sales[ProductKey].
| step | what to do |
|---|---|
| 1 | Make sure cardinality is 1:* and direction is Single |
| 2 | Go back to the visual — the numbers are separate |
| Category | Sales Amount | % of total |
|---|---|---|
| Bikes | $94,620,526.21 | 86.2% |
| Components | $11,799,076.66 | 10.7% |
| Clothing | $2,117,613.45 | 1.9% |
| Accessories | $1,272,057.89 | 1.2% |
✓ The total is still $109,809,274.20 — except now it's split correctly.
5 Dates — the active context
Date[DateKey] to Sales[OrderDateKey].
| step | what to do |
|---|---|
| 1 | Drag the relationship in Model view |
| 2 | Line chart: Fiscal Year vs Sales Amount |
| Fiscal Year | Sales Amount |
|---|---|
| FY2018 | $23,860,891.17 |
| FY2019 | $34,070,108.50 |
| FY2020 | $51,878,274.54 |
✓ Growth of 2.2x over three years — a story that disappears completely without context. FY2021 exists in the dates table but with no sales.
6 The three remaining dimensions
Customer, Reseller, and Sales Territory to Sales.
| step | what to do |
|---|---|
| 1 | CustomerKey · ResellerKey · SalesTerritoryKey — three 1:* relationships |
| 2 | Bar chart: Region vs. Sales Amount, descending sort |
| Region | Sales Amount |
|---|---|
| Southwest | $24,184,609.60 |
| Canada | $16,355,770.46 |
| Northwest | $16,084,942.55 |
| Australia | $10,655,335.96 |
✓ Notice: Corporate HQ appears in the dimension with no sales — a dimension row without a Fact is valid.
7 The 1:1 relationship — Sales Order
Sales[SalesOrderLineKey] to Sales Order[SalesOrderLineKey].
| step | what to do |
|---|---|
| 1 | Power BI will detect 1:1 cardinality |
| 2 | Donut: Channel vs. Sales Amount |
| Channel | Sales Amount | rows |
|---|---|---|
| Reseller | $80,450,596.98 | 60,855 |
| Internet | $29,358,677.22 | 60,398 |
✓ The number of rows is almost identical, but the wholesaler generates 73% of the money — much larger transactions.
8 Model check
Model view — arrange Sales in the center and the six dimensions around it.
| step | what to do |
|---|---|
| 1 | Count the relationships: six active |
| 2 | Make sure all the arrows go from the dimension to Sales, and are all Single |
✓ This is exactly a Star Schema: every dimension one hop away from the Fact.
9 Inactive relationship — ship date
Date[DateKey] to Sales[ShipDateKey] — will be marked dashed.
| step | what to do |
|---|---|
| 1 | Power BI will create it as inactive automatically — that's normal |
| 2 | Check in Power Query how many ShipDateKey rows are empty |
| check | result |
|---|---|
| Rows without a shipping date | 2,113 |
| Their sum | $1,281,073.41 |
| What will happen in the visual | will fall into a blank row (Blank) |
✓ Orders that haven't shipped yet. This isn't a bug — it's a business reality the model must reflect.
10 The inactive relationship — what it will change
Manage relationships — check the relationship on the shipping date.
| step | what to do |
|---|---|
| 1 | Open the relationships window and locate the relationship whose status is Inactive |
| 2 | Write down for yourself: which column on each side, what's the cardinality, and what's the filter direction |
| 3 | In the model view, make sure it is drawn with a dashed line, while the relationship on order date is drawn with a solid line |
✓ Only one active relationship is allowed between a pair of tables. The inactive one lies ready — it doesn't affect any visual until you activate it explicitly.
11 Products that were never sold
Table: Product[Product] and Sales Amount.
| step | what to do |
|---|---|
| 1 | Add Count (Distinct) on Sales[ProductKey] |
| 2 | Filter to rows where Sales Amount is blank |
| check | result |
|---|---|
| Products in the catalog | 397 |
| Products that were sold | 350 |
| Never sold | 47 |
✓ This is the answer to \"why not one giant table\": in a flat table these 47 products wouldn't exist.
12 Vendors with no sales
The same check on Reseller.
| step | what to do |
|---|---|
| 1 | Count (Distinct) on Sales[ResellerKey] versus the number of rows in Reseller |
| check | result |
|---|---|
| Marketers in the file | 702 |
| Vendors with sales | 636 |
| No sales | 66 |
✓ At the end: save the file. In a correct model, the numbers above must come out exactly.
Summary — the numbers you must get
| check | Verified result |
|---|---|
| Total sales | $109,809,274.20 |
| Bikes | $94,620,526.21 |
| FY2020 | $51,878,274.54 |
| Southwest | $24,184,609.60 |
| Reseller vs Internet | $80,450,596.98 vs $29,358,677.22 |
| Rows without a shipping date | 2,113 · $1,281,073.41 |
| Products that were sold | 350 out of 397 |
| Vendors with sales | 636 out of 702 |
All the questions deal with relationships: directionality, cardinality and active vs. inactive
Chapter X · What's next
Resources and next steps
To go deeper into modeling
You can also navigate with the buttons at the top of the screen, keyboard arrows, and on phone with a swipe