DAX RELATED, RELATEDTABLE and USERELATIONSHIP Explained
· 7 min read
The DAX RELATED function returns a value from the "one" side of a relationship for the current row on the "many" side. In a star schema, that means you are on a fact table row (one order line) and want a column from a dimension (the product's category or cost). RELATED needs a row context, so you use it in a calculated column or inside an iterator such as SUMX.
-- Calculated column on Sales
Category = RELATED ( Products[Category] )
-- Measure: iterator gives RELATED its row context
Cost = SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) )RELATEDTABLE goes the other direction (from a dimension row to its fact rows), and USERELATIONSHIP switches a measure to an inactive relationship. All three are shown below on a Brightline Supply Co. model.
The model
Brightline sells office, kitchen and cleaning supplies. The model:
- Sales (fact): OrderID, OrderDate, ShipDate, CustomerID, ProductID, Quantity, Line Revenue, Status.
- Products: ProductID, ProductName, Category, UnitCost. Relationship Products[ProductID] 1 to many Sales[ProductID].
- Customers: CustomerID, CustomerName, Segment, Region. Relationship Customers[CustomerID] 1 to many Sales[CustomerID].
- Date: one row per day of 2025. Active relationship to Sales[OrderDate], inactive relationship to Sales[ShipDate].
Six completed order lines. To keep the formulas short, assume Sales holds only completed lines. On the full data you would add a Status filter with CALCULATE, as shown in DAX CALCULATE explained.
| OrderID | OrderDate | ShipDate | Customer | Product | Quantity | Line Revenue |
|---|---|---|---|---|---|---|
| 50001 | 2025-01-06 | 2025-01-08 | Harbor & Pine Hotels | Espresso Beans 1kg | 4 | 96.00 |
| 50002 | 2025-01-14 | 2025-01-16 | Lakeside Studios | Copy Paper A4 (500) | 10 | 49.50 |
| 50003 | 2025-01-30 | 2025-02-03 | Pioneer Studios | Paper Towels (24) | 3 | 75.00 |
| 50004 | 2025-02-11 | 2025-02-13 | Harbor & Pine Hotels | Toner Cartridge Black | 2 | 112.10 |
| 50006 | 2025-03-05 | 2025-03-07 | Lakeside Studios | Tea Assortment (200) | 5 | 99.50 |
| 50007 | 2025-03-27 | 2025-04-01 | River Print Co | Paper Towels (24) | 8 | 180.00 |
The relevant Products rows:
| Product | Category | UnitCost |
|---|---|---|
| Espresso Beans 1kg | Coffee & Kitchen | 11.50 |
| Tea Assortment (200) | Coffee & Kitchen | 9.40 |
| Copy Paper A4 (500) | Paper & Print | 3.10 |
| Toner Cartridge Black | Paper & Print | 28.00 |
| Paper Towels (24) | Cleaning | 12.40 |
RELATED: get a value from a related table
The Cost measure from the top iterates Sales. For each row, RELATED ( Products[UnitCost] ) follows the relationship to that row's product. Per line: 46.00, 31.00, 37.20, 56.00, 47.00 and 99.20.
Revenue = SUM ( Sales[Line Revenue] )
Gross Margin = [Revenue] - [Cost]
Gross Margin % = DIVIDE ( [Gross Margin], [Revenue] )| Category | Revenue | Cost | Gross Margin | Gross Margin % |
|---|---|---|---|---|
| Coffee & Kitchen | 195.50 | 93.00 | 102.50 | 52.4% |
| Paper & Print | 161.60 | 87.00 | 74.60 | 46.2% |
| Cleaning | 255.00 | 136.40 | 118.60 | 46.5% |
| Total | 612.10 | 316.40 | 295.70 | 48.3% |
RELATED can follow a chain of many-to-one relationships, for example from Sales to Customers to a Regions table, as long as each step goes towards the "one" side.
Do you need RELATED to filter or group by category? No. Putting Products[Category] on the rows already filters Sales through the relationship. Use RELATED only when a calculation needs the dimension value row by row (cost, a list price, a flag), or when you want the value stored as a column on the fact table.
The SQL equivalent is a join: JOIN products p ON p.product_id = s.product_id, then p.unit_cost in the expression. See joining two tables in SQL.
RELATEDTABLE: from the one side to the many side
On a Customers row, there are many Sales rows. RELATEDTABLE ( Sales ) returns them as a table, so you wrap it in an aggregation:
-- Calculated columns on Customers
Completed Orders = COUNTROWS ( RELATEDTABLE ( Sales ) )
Customer Revenue = SUMX ( RELATEDTABLE ( Sales ), Sales[Line Revenue] )| Customer | Completed Orders | Customer Revenue |
|---|---|---|
| Harbor & Pine Hotels | 2 | 208.10 |
| Lakeside Studios | 2 | 149.00 |
| Pioneer Studios | 1 | 75.00 |
| River Print Co | 1 | 180.00 |
RELATEDTABLE is CALCULATETABLE under the hood: it turns the current Customers row into a filter (context transition) and returns the Sales rows that pass. That is why it works in a calculated column. As columns, these values are fixed at refresh, which is right if you want to segment customers ("customers with 2+ orders") in a slicer. For totals that react to slicers, write measures instead. The trade-off is in measure vs calculated column.
USERELATIONSHIP and inactive relationships
Power BI allows only one active relationship between two tables. Sales has two dates, so the Date table's relationship to Sales[OrderDate] is active and the one to Sales[ShipDate] is inactive (a dashed line in model view). Every measure uses the active one by default.
To report by ship date, activate the inactive relationship for one measure:
Revenue by Ship Date =
CALCULATE (
[Revenue],
USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)| Month | Revenue (by order date) | Revenue by Ship Date |
|---|---|---|
| Jan | 220.50 | 145.50 |
| Feb | 112.10 | 187.10 |
| Mar | 279.50 | 99.50 |
| Apr | 180.00 | |
| Total | 612.10 | 612.10 |
Order 50003 was placed on 30 January and shipped on 3 February, so it moves from January to February. Order 50007 moves from March to April. The total is the same, because the same orders are counted.
Rules for USERELATIONSHIP:
- It only works as a filter argument of CALCULATE or CALCULATETABLE (and functions that accept a filter argument, such as TOTALYTD).
- The relationship must already exist in the model, active or inactive. USERELATIONSHIP does not create one.
- The order of the two columns does not matter.
- While it is active in that CALCULATE, the other relationship between the same tables is ignored.
Common mistakes
RELATED in a measure with no iterator
AI assistants often write a margin measure like this:
Cost = SUM ( Sales[Quantity] ) * RELATED ( Products[UnitCost] )It fails, because a measure has no current row for RELATED to follow. The fix is the SUMX version at the top. How to spot it: RELATED must sit inside something that iterates rows (SUMX, FILTER, ADDCOLUMNS) or in a calculated column.
RELATED in the wrong direction
RELATED ( Sales[Quantity] ) in a calculated column on Products fails: a product has many Sales rows, so there is no single value to return. From the one side, use RELATEDTABLE with an aggregation.
USERELATIONSHIP outside CALCULATE
= USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) on its own, or inside SUM, is an error. It is a filter modifier, not a function that returns a value.
LOOKUPVALUE where a relationship exists
AI-written DAX often reaches for LOOKUPVALUE ( Products[UnitCost], Products[ProductID], Sales[ProductID] ). It returns the right number, but it is slower and ignores the model. If a relationship exists, use RELATED. Keep LOOKUPVALUE for tables you cannot relate.
Making both relationships active with a bi-directional filter
When ship-date reporting "does not work", a common suggestion is to change cross-filter directions until something shows up. That can create ambiguous paths that give different numbers. Keep one active relationship and use USERELATIONSHIP per measure.
FAQ
How do I get a value from a related table in DAX?
In a calculated column on the many side, use RELATED ( Table[Column] ). In a measure, iterate the many side: SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) ).
What is the difference between RELATED and RELATEDTABLE?
RELATED returns one value from the one side, for a row on the many side. RELATEDTABLE returns a table of rows from the many side, for a row on the one side.
How do I use an inactive relationship in Power BI?
Create the relationship in model view (it becomes inactive if an active one already exists), then write CALCULATE ( [Measure], USERELATIONSHIP ( Fact[Column], Dim[Column] ) ).
Why does RELATED not work in my measure?
A measure has no row context. RELATED needs one, so put it inside an iterator like SUMX, or use it in a calculated column.
Does RELATED work with many-to-many relationships?
No. RELATED needs a single value on the other side, so it only works towards the one side of a one-to-many (or one-to-one) relationship.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Cost of goods sold” →