RELATED/RELATEDTABLE — Use RELATED/RELATEDTABLE for relationship navigation (intro)

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.

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.

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.

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

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

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.

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.

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.

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.

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.

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.