Historical Context & Motivation
Relational databases have long relied on JOIN operations to combine rows from two or more tables based on a related column. When Microsoft introduced Power Pivot in 2010 and later expanded it into Power BI's in-memory columnar engine, the underlying philosophy shifted away from explicit SQL joins toward a model-driven navigation paradigm. Instead of writing JOIN clauses, analysts define relationships once in the data model, and the DAX language provides functions—chiefly RELATED and RELATEDTABLE—that traverse those relationships at query time. This design echoes the Entity-Relationship model formalized by Peter Chen in 1976, but implements it inside a columnar, compressed analytics engine optimized for aggregation rather than transactional throughput.
The central question these functions address is deceptively simple: how do you access a value that lives in a different table from the one your current row context belongs to? In SQL you would write a JOIN, but DAX operates within a filter-context and row-context evaluation model where relationships are metadata, not runtime clauses. Understanding RELATED and RELATEDTABLE is therefore foundational to writing correct and performant DAX.
Core Principles & Definitions
Before diving into syntax, it is essential to understand the data model primitives that make RELATED and RELATEDTABLE possible. Power BI's analytical model organizes tables into a star schema (or snowflake variant) where fact tables store transactional or event-level data and dimension tables store descriptive attributes. Relationships between tables are defined by matching key columns, and each relationship has a cardinality (one-to-many, one-to-one, many-to-many) and a cross-filter direction that governs how filters propagate.
Row Context
SUMX. RELATED and RELATEDTABLE both require an active row context to know which foreign key to follow.Many-to-One (RELATED)
One-to-Many (RELATEDTABLE)
Filter Propagation
Relationship Chains
Visual Explanation — Star Schema Navigation
The visual makes the directional nature of these functions explicit. When your row context is in the Sales table (the many side), calling RELATED(Product[ProductName]) follows the foreign key ProductID to the unique row in the Product dimension and returns a single scalar. Conversely, if your row context is in the Product table (the one side), calling RELATEDTABLE(Sales) gathers all Sales rows whose ProductID matches the current product, returning a table. This distinction—scalar return versus table return—is the fundamental differentiator between the two functions and dictates where each can appear in a DAX expression.
How It Works — Evaluation Mechanics
To understand the internal mechanics, consider the evaluation model that the VertiPaq engine uses. Every DAX expression is evaluated within two orthogonal contexts: the filter context (which rows are visible) and the row context (which single row is currently being iterated). Both RELATED and RELATEDTABLE operate exclusively within a row context—they need a 'current row' to know which key value to follow. This is why these functions are typically used inside calculated columns (which always have a row context) or inside iterator functions like SUMX, AVERAGEX, and FILTER.
RELATED — Scalar Lookup Semantics
<column> is a fully qualified column reference (e.g., Product[ProductName]) in a table on the one side of a relationship. Returns a single scalar value of the same data type as the referenced column.Internally, the engine resolves RELATED by reading the foreign key column of the current row, performing a hash lookup against the primary key index of the target dimension table, and returning the requested column's value. Because the target is guaranteed to have at most one matching row (the relationship is many-to-one), the return type is always a scalar. If no matching row exists—e.g., a dangling foreign key—RELATED returns BLANK(). Importantly, RELATED can chain across multiple hops: RELATED(Category[CategoryName]) called from a Sales row context will automatically traverse Sales → Product → Category, as long as each intermediate relationship is many-to-one.
RELATEDTABLE — Table-Valued Lookup Semantics
<table> is the name of a table on the many side of a relationship relative to the current row context. Returns a table filtered to only those rows whose foreign key matches the current row's primary key.RELATEDTABLE is semantically equivalent to CALCULATETABLE(<table>) when a row context is active on the one side. Under the hood, the engine performs a context transition: it takes the primary key of the current row, converts it into a filter argument, and applies that filter to the target table. Because the target is on the many side, the result can contain zero, one, or many rows. This is why RELATEDTABLE must be wrapped in an aggregation function (COUNTROWS, SUMX, etc.) whenever a scalar result is needed.
SUMX(Sales, Sales[Qty] * RELATED(Product[Price])). Alternatively, use RELATED freely in calculated columns, which always have an implicit row context.Detailed Breakdown — Contexts, Directions & Patterns
The choice between RELATED and RELATEDTABLE depends on two factors: which table your row context belongs to and the cardinality of the relationship you intend to traverse. The following diagram and table formalize this decision process.
RELATED) or the one side (use RELATEDTABLE)? The bottom panel shows typical code patterns for each scenario.| Aspect | RELATED | RELATEDTABLE |
|---|---|---|
| Direction | Many → One (follows FK to PK) | One → Many (gathers rows by PK) |
| Return type | Scalar (single value) | Table (set of rows) |
| Multi-hop | Yes — can chain across multiple M:1 relationships | No — single hop only |
| Typical use | Calculated column on fact table; iterator in a measure | Calculated column on dimension table; argument to COUNTROWS, SUMX |
| SQL analogy | LEFT JOIN … ON FK = PK (returning one column) | Correlated subquery returning a filtered rowset |
Worked Example — Building a Revenue Measure
Suppose we have a Sales fact table with columns SaleID, ProductID, Quantity, and Discount, and a Product dimension table with ProductID, ProductName, and UnitPrice. We want to create a measure that computes total net revenue, and a calculated column on the Product table that counts the number of sales per product.
UnitPrice lives in the Product table, we need RELATED to bring it into a Sales row context.SUMX to iterate over Sales rows, providing the row context that RELATED requires:Net Revenue = SUMX( Sales, Sales[Quantity] × RELATED(Product[UnitPrice]) × (1 − Sales[Discount]) )RELATED(Product[UnitPrice]) reads the current row's ProductID, finds the matching Product row, and returns UnitPrice. The expression multiplies Quantity × UnitPrice × (1 − Discount) per row, and SUMX aggregates the results.SalesCount = COUNTROWS( RELATEDTABLE(Sales) )ProductID. RELATEDTABLE(Sales) filters the Sales table to only those rows whose ProductID matches, and COUNTROWS counts them. If product 42 appears in 150 Sales rows, the result is Strengths, Limitations & Alternatives
RELATED and RELATEDTABLE are elegant for navigating established model relationships, but they are not the only tools available in DAX. Understanding when to use them—and when to reach for alternatives—is critical for writing maintainable, performant expressions.
| Consideration | Strengths | Limitations |
|---|---|---|
| Readability | Intuitive syntax; the relationship is implicit and the code is self-documenting. | Multi-hop chains can be opaque if the model is unfamiliar to the reader. |
| Performance | Leverages pre-built hash indexes on relationship columns; very fast for VertiPaq. | RELATEDTABLE on very large fact tables inside a row-by-row iterator can be slow; CALCULATE may be more efficient. |
| Flexibility | Works automatically with any active relationship; no key columns need to be specified. | Cannot use inactive relationships without USERELATIONSHIP; cannot handle virtual relationships (no model relationship defined). |
| Many-to-Many | N/A | Neither function works directly with many-to-many cardinality; bridge tables or TREATAS are needed. |
LOOKUPVALUE (for ad-hoc scalar lookups) or TREATAS (for virtual relationships).Connection to Advanced DAX Patterns
RELATED and RELATEDTABLE form the introductory layer of a broader DAX pattern family centered on context transition and expanded tables. As your models grow in complexity—with role-playing dimensions, many-to-many bridges, and calculation groups—you will encounter scenarios where these introductory functions must yield to more powerful constructs.
| Feature | RELATED / RELATEDTABLE | Advanced Alternative |
|---|---|---|
| Inactive relationships | Cannot use inactive relationships natively. | CALCULATE( ..., USERELATIONSHIP(OrderDate, Date[Date]) ) |
| Virtual relationships | Requires a physical model relationship. | TREATAS( VALUES(Budget[ProductID]), Product[ProductID] ) |
| Context transition | RELATEDTABLE performs implicit context transition. | CALCULATE( SUM(Sales[Amount]) ) performs explicit context transition within iterators. |
| Many-to-many | Not supported. | Bridge tables with bidirectional filtering or CROSSFILTER. |
In subsequent lessons, you will explore how CALCULATE subsumes much of what RELATEDTABLE does via context transition, and how USERELATIONSHIP extends navigation to inactive paths—particularly useful in role-playing dimension scenarios such as a Date table linked to both OrderDate and ShipDate. Mastering RELATED and RELATEDTABLE now gives you the conceptual vocabulary—row context, relationship direction, scalar vs. table return—that underpins these advanced techniques.
Practice Problems
RELATED always returns a scalar value while RELATEDTABLE returns a table. How does the cardinality of the relationship determine the return type?Quantity and ProductID, and a Product table with columns ProductID and Weight, write a DAX measure that calculates total shipped weight across all sales.Amount column. Write the DAX expression and explain why you chose RELATEDTABLE instead of RELATED.CALCULATETABLE(<table>) inside a row context (relying on context transition). Critically evaluate this claim: under what conditions are they equivalent, and can you identify a scenario where the choice between them has practical implications?Lesson Summary
In this lesson you learned that RELATED and RELATEDTABLE are the primary DAX functions for navigating relationships in a Power BI data model. RELATED follows many-to-one relationships and returns a scalar value from the lookup (dimension) table. RELATEDTABLE traverses one-to-many relationships and returns a table of matching rows from the fact table. Both functions require a row context to operate, which is provided by calculated columns or iterator functions like SUMX and FILTER.
The key decision rule is straightforward: if your current row context is on the many side and you need a value from the one side, use RELATED; if you are on the one side and need rows from the many side, use RELATEDTABLE. RELATED supports multi-hop chaining across consecutive many-to-one relationships, while RELATEDTABLE is limited to a single hop. Looking ahead, CALCULATE and USERELATIONSHIP extend these navigation capabilities to inactive relationships and more complex filter manipulations.