Understanding how DAX expands tables and propagates filter context across relationships is the key to building reliable measures in complex schemas. This lesson teaches you exactly how RELATED and RELATEDTABLE traverse multi-hop relationship chains — and how to debug it when things go wrong.

You've built a clean star schema. Sales facts, a date table, a product dimension, a customer dimension — the classic setup. Your basic measures work fine. Then your data model grows: product categories get their own table, regions roll up into territories which roll up into areas, and suddenly a finance team hands you a snowflake schema with four levels of dimension joins. Your measures start returning unexpected blanks. RELATED stops working where you're sure it should. Filter context seems to vanish before it reaches the table you need. Sound familiar?
This is the moment where most Power BI practitioners hit a wall. They understand filter context in isolation, but the mechanics of how that context travels across relationships — and the precise conditions under which it does or doesn't — remain fuzzy. The good news is that this behavior is completely deterministic. Once you understand the internal machinery of expanded tables, relationship propagation direction, and how RELATED and RELATEDTABLE actually invoke that machinery, you'll be able to predict the behavior of any measure in any schema just by reading the model diagram.
By the end of this lesson, you'll be able to write measures that correctly traverse multi-hop relationships, debug filter propagation failures, use RELATED and RELATEDTABLE with full confidence in both star and snowflake schemas, and understand the performance implications of your design choices.
What you'll learn:
You should be comfortable with:
Before we touch RELATED or RELATEDTABLE, we need to talk about something that never appears in the UI but governs everything: expanded tables.
When the DAX engine evaluates a query, it doesn't just see your raw physical tables. It sees expanded versions of them. An expanded table is conceptually a physical table joined with all tables reachable from it by following many-to-one relationships — in other words, all the dimension data that "belongs to" each row on the many side.
Consider this simplified sales model:
Sales (FactTable)
- SalesOrderID
- ProductKey → Product[ProductKey]
- CustomerKey → Customer[CustomerKey]
- DateKey → Date[DateKey]
- Quantity
- UnitPrice
Product
- ProductKey
- ProductName
- CategoryKey → ProductCategory[CategoryKey]
ProductCategory
- CategoryKey
- CategoryName
- DivisionKey → Division[DivisionKey]
Division
- DivisionKey
- DivisionName
In this snowflake schema, the Sales table's expanded form conceptually includes every column from Product, ProductCategory, Division, Customer, and Date. The DAX engine treats the Sales table as if it were one enormous flattened table with all those columns attached.
Key insight
This is why filter context works the way it does. When you filter Division[DivisionName] = "Electronics", that filter doesn't stop at the Division table. It propagates down the relationship chain: Division → ProductCategory → Product → Sales. The filter effectively becomes a constraint on which Sales rows are visible.
This propagation is automatic and happens in the direction of the relationship arrows. The expanded table concept explains it cleanly: filtering the Sales expanded table by a column that "lives" in one of its expanded dimensions is completely natural.
What doesn't happen automatically: propagation in the reverse direction (from Sales back up to Product, for instance) unless you've configured bidirectional cross-filter. We'll come back to that.
RELATED is a row context function. It lets you retrieve a column value from a related table by following a many-to-one relationship. The key phrase is row context — RELATED only works when there's an active row context, which means inside calculated columns or inside iterator functions.
Let's say you want to add a CategoryName column directly to your Sales table for convenience (we'll talk about whether this is advisable in the performance section — but it's instructive to see):
Sales[CategoryName] =
RELATED( ProductCategory[CategoryName] )
For every row in Sales, DAX follows the chain:
Sales[ProductKey] → matches a row in ProductProduct[CategoryKey] → matches a row in ProductCategoryProductCategory[CategoryName]Notice that this is a two-hop traversal. RELATED handles the entire chain transparently — you don't need to call it twice or explicitly specify the intermediate table. The engine knows the path because the relationships are defined in the model.
Warning
RELATED only works in the many-to-one direction. If you try to call RELATED(Sales[Quantity]) from inside the Product table, you'll get an error because Product is on the one-side of that relationship. Product rows relate to multiple Sales rows, so RELATED can't return a single scalar value.
Where RELATED really earns its keep is inside iterators in measures. This is where many practitioners get confused about when row context exists.
Suppose you need to calculate revenue at the unit level, applying a discount percentage that lives in the ProductCategory table:
Revenue After Category Discount =
SUMX(
Sales,
Sales[Quantity]
* Sales[UnitPrice]
* ( 1 - RELATED( ProductCategory[DiscountPct] ) )
)
As SUMX iterates through each row of Sales, row context is active. RELATED can follow the chain Sales → Product → ProductCategory to retrieve the discount percentage for that specific row. This is elegant and efficient.
Tip
A common confusion: why can't you just write SUM(Sales[Quantity]) * SUM(Sales[UnitPrice]) to get revenue? Because that's mathematically wrong — you can't multiply aggregations. SUMX gives you row-level multiplication and then sums the results. This is a great example of why understanding row context matters. The DAX Iterators Explained lesson covers this pattern in depth.
Here's the scenario that trips people up. You have your snowflake: Sales → Product → ProductCategory → Division. You need the DivisionName for each Sales row in a calculated column:
Sales[DivisionName] =
RELATED( Division[DivisionName] )
This works. DAX walks the entire chain automatically. The rule is: as long as there's an unbroken chain of many-to-one relationships from the current table to the target table, RELATED can reach it in one call.
What breaks this? Any of the following:
RELATEDTABLE is the inverse of RELATED. Where RELATED fetches a single value by going many-to-one, RELATEDTABLE returns an entire table of rows by going one-to-many.
This function is most commonly encountered in calculated columns on the one side of a relationship, where you want to know something about the many side rows that belong to the current row.
A simple but illustrative example: calculating how many distinct products exist in each category, directly in the ProductCategory table.
ProductCategory[ProductCount] =
COUNTROWS( RELATEDTABLE( Product ) )
For each row in ProductCategory, RELATEDTABLE returns the subset of Product rows where Product[CategoryKey] matches the current ProductCategory[CategoryKey]. Then COUNTROWS counts them.
Here's a more sophisticated version. Suppose your Product table has a IsActive flag, and you want each ProductCategory row to know how many of its products are currently active:
ProductCategory[ActiveProductCount] =
COUNTROWS(
FILTER(
RELATEDTABLE( Product ),
Product[IsActive] = TRUE()
)
)
RELATEDTABLE returns the full set of related Product rows, and then FILTER narrows it down. This pattern is powerful because it lets you perform arbitrary aggregations on the related many-side table.
Here's where things get interesting. RELATEDTABLE in measures behaves differently than in calculated columns, because measures don't automatically have row context.
If you try to use RELATEDTABLE directly in a measure at the top level:
-- This works but may not do what you expect
Product Sales Count =
COUNTROWS( RELATEDTABLE( Sales ) )
When this measure evaluates without any explicit row context, the "current row" of Product doesn't exist. The measure only makes sense when it's being evaluated in a visual that is broken down by Product rows — in that case, the visual's grouping creates something like a row context (actually a filter context per row), and RELATEDTABLE effectively returns the Sales rows that match the current Product filter.
Key insight
In a measure context, RELATEDTABLE is more accurately a shorthand for filtering the related table by the current filter context. It's functionally equivalent to FILTER(Sales, RELATED(Product[ProductKey]) = <current key>), but the engine handles that join implicitly. This is why it still works inside measures — the filter context does the work that row context does in calculated columns.
Understanding which direction filters flow is essential for debugging unexpected blanks and incorrect totals. Let's lay out the rules precisely.
In a standard relationship, filters flow from the one side to the many side. This is the most natural direction: filtering a dimension filters the fact table.
Division (1) → ProductCategory (many)
ProductCategory (1) → Product (many)
Product (1) → Sales (many)
If a user selects "Electronics" in a slicer connected to Division[DivisionName], that filter propagates all the way through the chain to Sales. Every measure on Sales automatically respects this filter.
What does not happen by default: filtering Sales does not filter Division. If you put a date filter on Sales, the Division slicer doesn't respond to it (unless you've set up bidirectional filtering).
Bidirectional relationships allow filters to flow in both directions. This can seem appealing — it means filtering Sales could dynamically filter your dimension slicers to show only values that have sales data.
But bidirectional filtering in complex models creates serious risks:
Warning
Microsoft's official guidance, and the consensus among experienced DAX practitioners, is to avoid bidirectional relationships in complex models whenever possible. Use CROSSFILTER inside specific measures when you need reverse propagation for a specific calculation. See Mastering DAX CROSSFILTER and USERELATIONSHIP for the controlled approach.
The multi-hop propagation in snowflakes is where many practitioners expect problems but actually find the engine behaves well — as long as relationships are single-direction and properly chained.
Using our Sales → Product → ProductCategory → Division chain:
A filter on Division[DivisionName] propagates:
This all happens automatically. Your [Total Sales] measure doesn't need to know about the Division table at all — the filter context handles it.
Where snowflakes can cause problems is when you have multiple paths from one table to another. If Sales connects to Date via both OrderDate and ShipDate, you have a many-to-one relationship through two different keys. The engine can only use one relationship as active at a time. For the inactive one, you need USERELATIONSHIP inside CALCULATE.
Let's build something concrete. We have a retail analytics model with this snowflake structure:
Sales
- SalesID, ProductKey, StoreKey, DateKey, CustomerKey
- Quantity, GrossAmount, DiscountAmount
Product
- ProductKey, ProductName, SubcategoryKey, CostPrice
ProductSubcategory
- SubcategoryKey, SubcategoryName, CategoryKey, TargetMarginPct
ProductCategory
- CategoryKey, CategoryName, BudgetAmount (annual)
Store
- StoreKey, StoreName, RegionKey
Region
- RegionKey, RegionName, TerritoryKey
Territory
- TerritoryKey, TerritoryName
Date (standard date dimension)
This is a realistic snowflake schema — four levels deep on the product side, three levels on the geography side.
Net Revenue =
SUMX(
Sales,
Sales[GrossAmount] - Sales[DiscountAmount]
)
Simple. Filters from any level of the hierarchy — ProductCategory, Region, Territory — flow down to Sales automatically. No special handling needed.
You want gross margin at the transaction level, requiring the cost price from Product:
Gross Margin % =
DIVIDE(
SUMX(
Sales,
( Sales[GrossAmount] - Sales[DiscountAmount] )
- ( Sales[Quantity] * RELATED( Product[CostPrice] ) )
),
SUMX(
Sales,
Sales[GrossAmount] - Sales[DiscountAmount]
)
)
RELATED here walks one hop from Sales to Product. For each row iteration, it retrieves the unit cost for the specific product on that sales line.
The annual budget lives in ProductCategory, which is three hops away from Sales. How do you compare actual revenue to that budget?
Budget Achievement % =
VAR ActualRevenue = [Net Revenue]
VAR CategoryBudget =
SUMX(
VALUES( ProductCategory[CategoryKey] ),
RELATED( ProductCategory[BudgetAmount] )
)
RETURN
DIVIDE( ActualRevenue, CategoryBudget )
Wait — there's a problem here. RELATED(ProductCategory[BudgetAmount]) won't work inside a SUMX over ProductCategory because we're on the ProductCategory table, not on a table that has ProductCategory on its many-side. We're trying to access ProductCategory columns while iterating ProductCategory rows — that's just a direct column reference, not RELATED.
Let me correct this to show the right pattern:
Budget Achievement % =
VAR ActualRevenue = [Net Revenue]
VAR CategoryBudget =
SUMX(
VALUES( ProductCategory[CategoryKey] ),
MAXX(
FILTER(
ProductCategory,
ProductCategory[CategoryKey] = EARLIER( ProductCategory[CategoryKey] )
),
ProductCategory[BudgetAmount]
)
)
RETURN
DIVIDE( ActualRevenue, CategoryBudget )
Actually, the cleaner approach here leverages the fact that filter context automatically flows to ProductCategory:
Budget Achievement % =
DIVIDE(
[Net Revenue],
CALCULATE( SUM( ProductCategory[BudgetAmount] ) )
)
Since filter context from slicers (say, a Category slicer) automatically propagates to ProductCategory, SUM(ProductCategory[BudgetAmount]) already respects the current filter. This is the elegance of proper filter propagation — you don't need to manually join; the model does it.
Tip
When you find yourself writing complex FILTER logic to restrict a dimension table, pause and ask: does the current filter context already handle this? Often it does, and simpler DAX is both more readable and faster.
Now let's work from the Territory level downward. You want a calculated column on Territory that shows the number of distinct customers who bought within that territory:
Territory[UniqueCustomerCount] =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
RELATEDTABLE( Sales ),
"CustKey", Sales[CustomerKey]
)
)
)
This follows the chain: Territory → Region → Store → Sales, and returns all Sales rows connected to stores in this territory. SELECTCOLUMNS extracts just the CustomerKey column, DISTINCT deduplicates, and COUNTROWS gives the final count.
Alternatively, you could use:
Territory[UniqueCustomerCount] =
DISTINCTCOUNT_X: -- this doesn't exist, so:
COUNTROWS(
DISTINCT(
RELATEDTABLE( Sales )[CustomerKey]
)
)
That column-accessor syntax is cleaner:
Territory[UniqueCustomerCount] =
COUNTROWS(
DISTINCT(
RELATEDTABLE( Sales )[CustomerKey]
)
)
This measure checks whether each category is meeting its target margin (stored in ProductSubcategory[TargetMarginPct]):
Below Margin Target =
VAR CurrentMargin = [Gross Margin %]
VAR SubcategoryTargets =
ADDCOLUMNS(
VALUES( ProductSubcategory[SubcategoryKey] ),
"TargetPct", MAXX(
RELATEDTABLE( ProductSubcategory ),
ProductSubcategory[TargetMarginPct]
)
)
RETURN
-- simplified: return 1 if any subcategory is below target
IF(
CurrentMargin < AVERAGEX(
VALUES( ProductSubcategory[SubcategoryKey] ),
MAXX(
FILTER(
ProductSubcategory,
ProductSubcategory[SubcategoryKey]
= EARLIER( ProductSubcategory[SubcategoryKey] )
),
ProductSubcategory[TargetMarginPct]
)
),
"Below Target",
"On Target"
)
This gets complex fast. In production, using DAX Variables in Practice to break this apart is essential for maintainability.
In our model, a report might have slicers for both Territory and ProductCategory. These are two completely independent filter chains that both converge on Sales:
Both filters apply simultaneously. The effective Sales rowset is the intersection of rows that satisfy both dimension filters. This is the magic of the expanded table concept: each filter independently constrains a different set of "columns" in the Sales expanded table, and the rows returned are those satisfying all constraints.
No special DAX needed. Your [Net Revenue] measure works correctly with any combination of filters from either dimension chain.
Sometimes you need to deliberately ignore part of the relationship filter. For example, to calculate market share within a territory regardless of the product category filter:
Market Share Within Territory =
DIVIDE(
[Net Revenue],
CALCULATE(
[Net Revenue],
ALL( ProductCategory ),
ALL( ProductSubcategory ),
ALL( Product )
)
)
The ALL() calls remove the product dimension filters while keeping the territory filter intact. The denominator represents total revenue in the current territory across all products. This is a direct application of the DAX Table Functions Explained patterns.
Suppose Sales has both an OrderDateKey and a ShipDateKey, both relating to the Date table. Only the OrderDate relationship is active. If you need to analyze by ship date:
Revenue by Ship Date =
CALCULATE(
[Net Revenue],
USERELATIONSHIP( Sales[ShipDateKey], Date[DateKey] )
)
USERELATIONSHIP temporarily activates the inactive relationship for the duration of this CALCULATE evaluation. This works in snowflake schemas just as well as star schemas — the relationship chain through Date is what changes, not the product or store chains.
Note
You cannot have both date relationships active simultaneously in a single measure. If you need to filter by both order date and ship date in the same calculation, you need virtual tables or a more complex approach. Consult Mastering DAX CROSSFILTER and USERELATIONSHIP for strategies.
Using the retail model described in the Real-World Project section (or build it yourself in Power BI Desktop with sample data), complete the following:
Exercise 1: Multi-Hop RELATED in a Calculated Column
Add a calculated column to Sales called TerritoryName that retrieves the territory name for each sale by traversing Sales → Store → Region → Territory. Verify it works for all rows and check for blanks (blank values indicate a broken relationship chain — investigate which StoreKey values have no matching Region entry).
Exercise 2: RELATEDTABLE Aggregation
In the ProductCategory table, add a calculated column AvgProductCost that calculates the average cost price of all products in that category by traversing ProductCategory → ProductSubcategory → Product and averaging Product[CostPrice]. Hint: you'll need AVERAGEX with RELATEDTABLE returning the Product table.
ProductCategory[AvgProductCost] =
AVERAGEX(
RELATEDTABLE( Product ),
Product[CostPrice]
)
Does this require intermediate hops? Test whether RELATEDTABLE skips the ProductSubcategory layer automatically.
Exercise 3: Filter Context Verification
Create a measure called [Category Revenue Share] that calculates the current category's revenue as a percentage of total revenue across all categories, while keeping any Territory filter intact:
Category Revenue Share =
DIVIDE(
[Net Revenue],
CALCULATE(
[Net Revenue],
ALL( ProductCategory ),
ALL( ProductSubcategory ),
ALL( Product )
)
)
Place this in a matrix with ProductCategory on rows and Territory on columns. Verify that each column sums to 100% (or close to it), confirming that the Territory filter is preserved while the Category filter is removed in the denominator.
Exercise 4: Snowflake Propagation Debugging
Intentionally break a relationship: disconnect ProductSubcategory from ProductCategory in the model. Observe what happens to measures that previously relied on Category-level filtering. Document which measures break and which continue working, and explain why based on the expanded table concept.
Symptom: RELATED returns an error: "The column 'X' either doesn't exist or doesn't have a relationship to any table available in the current context."
Cause: You're attempting RELATED from the one-side toward the many-side, or the relationship doesn't exist in the model.
Fix: Confirm in the model diagram that you're on the many-side of the relationship. Use RELATEDTABLE if you need to go the other direction.
Symptom: A calculated column using COUNTROWS(RELATEDTABLE(Sales)) returns the same large number for every row in a dimension table.
Cause: The relationship between the two tables isn't properly defined, so RELATEDTABLE has no filter to apply and returns all Sales rows.
Fix: Verify the relationship exists in the model and that the keys are the correct data types on both sides. Integer-to-string mismatches are a common culprit.
Symptom: A slicer on a high-level dimension (like Division) doesn't seem to filter your measure correctly.
Cause: Often a broken or missing relationship in the chain. Can also be caused by a table that's connected through a many-to-many relationship without a bridge table.
Fix: Use DAX Query View to write a diagnostic EVALUATE query that checks whether the filter is reaching the intermediate table. For example:
EVALUATE
CALCULATETABLE(
VALUES( ProductCategory[CategoryName] ),
Division[DivisionName] = "Electronics"
)
If this returns no rows, the filter isn't propagating from Division to ProductCategory, and you have a relationship problem.
Symptom: A measure that should sum to a known total at the grand total level returns a lower number. Or a measure changes value depending on what other visuals are on the page.
Cause: Bidirectional relationships are creating unexpected cross-filtering, causing the denominator or scope of a measure to be inadvertently restricted by filters from an unrelated visual.
Fix: Audit all bidirectional relationships in your model and replace them with single-direction relationships plus explicit CROSSFILTER calls in measures that genuinely need reverse propagation.
Symptom: RELATED causes an error in a measure: "RELATED function only works when there is one row in the current context."
Cause: You've used RELATED at the top level of a measure, where no row context exists.
Fix: Wrap the RELATED call inside an iterator (SUMX, AVERAGEX, MAXX, etc.) that establishes row context, or reconsider your approach — if you're trying to retrieve a single value, you likely want LOOKUPVALUE or SELECTEDVALUE instead. See the DAX Lookup Functions in Practice article for the full comparison.
Warning
A subtle variant of this mistake: using RELATED inside a measure that's being evaluated in a visual grouped by a column from the related table. This can appear to work (the visual grouping creates a filter context that "simulates" a row context) but will fail in different contexts. Always use iterators to be explicit about row context.
Symptom: RELATEDTABLE(Sales) in a calculated column seems to include sales from outside what you expected.
Explanation: In calculated columns, RELATEDTABLE filters by the relationship key only. It doesn't apply additional filter context from whatever visual or slicer might be on a report page — calculated columns are computed at refresh time, not at query time. This is expected behavior, not a bug.
Fix: If you need filter-aware aggregations, use measures instead of calculated columns. This is the core tradeoff discussed in DAX Fundamentals: When to Use Calculated Columns vs Measures in Power BI.
Calculated columns using RELATED are computed at refresh time and stored in memory. The relationship traversal is essentially a hash join performed once per row. This is generally efficient and doesn't impact query time.
RELATEDTABLE returning large row sets from fact tables can be expensive during refresh. A ProductCategory with 50 categories, each connecting to 100,000 sales rows, means evaluating 5 million row-context operations. For large models, consider whether this aggregation should be a measure instead.
The VertiPaq engine is highly optimized for the relationship traversal that filter propagation represents. Multi-hop chains in well-designed snowflake schemas typically perform well because the engine processes these as bitmap operations on compressed column stores.
What kills performance is non-sargable filters — conditions that prevent the engine from using its dictionary-encoded columns efficiently. Keep relationship keys as integers (not strings), avoid calculated key columns on join fields, and avoid using FILTER with complex conditions when simple column filters would do.
Tip
If you suspect relationship propagation is causing performance issues, use DAX Studio's server timings to inspect the Storage Engine query plan. You'll see the exact joins and scans being performed, which will tell you whether the bottleneck is in the relationship traversal or elsewhere.
The snowflake schema is normalized for data integrity, but sometimes denormalizing into a star schema improves DAX simplicity and performance. If you find yourself writing many multi-hop RELATED expressions or if storage engine queries show expensive join operations, consider flattening dimension tables.
A common pattern: keep the snowflake in the data warehouse but denormalize into a flattened dimension view in Power Query before loading to Power BI. This gives you the integrity of the source model and the performance of the star schema in your analytical layer.
You've now seen the full picture of how DAX handles multi-table models internally. The expanded table concept is the foundation — it explains why filters flow naturally across relationships, why RELATED works in row context, and why RELATEDTABLE returns a contextually filtered subset. The direction rules are simple: filters flow from one to many by default, and you use CROSSFILTER or USERELATIONSHIP when you need exceptions.
RELATED is your tool for fetching dimension attributes in row context — perfect for calculated columns and iterators. RELATEDTABLE is your tool for aggregating or counting the many-side rows that belong to a given dimension row. Together they give you full traversal capability across any properly modeled schema.
For snowflake schemas specifically, trust the chain. As long as relationships are properly defined and in the right direction, the DAX engine handles multi-hop traversal automatically. Your job is to design clean relationship chains and write measures that work with the filter context rather than against it.
Where to go next: