Module 4: DAX basics · Lesson 4.4 · about 14 min
RELATED and looking things up
RELATED fetches a value from the one side of a relationship for the current row. Use it in calculated columns and inside iterators, and know when LOOKUPVALUE or RELATEDTABLE is the right tool.
Module 4 · about 14 minutes · read first: 4.3. You’ll be able to look up values across relationships in columns and iterators.
1. Why this matters
Calculations often need a value from another table: the list price from Product, the region from Store. RELATED gets it without merging tables, which keeps the model a clean star.
2. RELATED inside an iterator
Product[UnitPrice] holds the 2024 list price, while Sales[UnitPrice] holds what was actually charged. Compare them:
Product[UnitPrice] is the 2024 list price. Write At List Price: each line's Quantity × the product's list price from Product, using RELATED inside SUMX. Split by Year: in which year did Northbay charge more than list?
Press Run to see the result.
Inside SUMX(Sales, ...) there is a current Sales row, so RELATED(Product[UnitPrice]) can fetch that row's product price.
Total Sales = SUM(Sales[Amount])
At List Price = SUMX(Sales, Sales[Quantity] * RELATED(Product[UnitPrice])) In 2025 Northbay charged about 4 % more than the 2024 list price, so Total Sales is higher than At List Price for 2025. Show me what happened on a 2025 row shows SUMX working out a list-price value for every line.
3. RELATED in a calculated column
Add a calculated column Sales[Region] with the store's region, then the query counts lines per region.
Press Run to see the result.
Sales[Region] = RELATED(Store[Region])
EVALUATE SUMMARIZECOLUMNS(Sales[Region], "Lines", COUNTROWS(Sales)) This works, but think twice before adding it to a real model: the relationship already lets Store[Region] filter Sales. A copied column only earns its place when you need it inside a Sales-level calculation.
4. Other lookups
| Function | Direction | Use |
|---|---|---|
RELATED(Dim[col]) |
many → one, one value | inside Sales rows, fetch the product’s category |
RELATEDTABLE(Fact) |
one → many, a table | in a Product column: COUNTROWS(RELATEDTABLE(Sales)) |
LOOKUPVALUE(result, search col, value) |
no relationship needed | fetch from a table with no relationship (slower, use sparingly) |
Principle
Principle: a many-to-one relationship guarantees exactly one row on the one side for each row on the many side, so a lookup from Sales to Product always gives one value. The other way gives a set of rows, so it returns a table.
Try it: in a Store calculated column, what does COUNTROWS(RELATEDTABLE(Sales)) give for Forks Market? (Its number of order lines, all in 2025.)
5. Worked example
Discount compared with list price. Discount Given = SUMX(Sales, Sales[Quantity] * (RELATED(Product[UnitPrice]) - Sales[UnitPrice])). For 2024 it’s 0 (prices equal the list); for 2025 it’s negative, which tells you prices rose. A real retailer would use this to track promotions.
6. Practice
- Write a Sales calculated column
Line Cost= Quantity × the product’s UnitCost from Product. - In a Product calculated column, count the stores that sold the product. (Hint: DISTINCTCOUNT on a RELATEDTABLE needs CALCULATE; try
CALCULATE(DISTINCTCOUNT(Sales[StoreKey])), Lesson 5.5 explains why.) - Why does RELATED fail in a plain measure?
Answers
Sales[Line Cost] = Sales[Quantity] * RELATED(Product[UnitCost]).Product[Stores] = CALCULATE(DISTINCTCOUNT(Sales[StoreKey])): CALCULATE turns the current product row into a filter on Sales.- A measure has no current row, so there’s no row to follow the relationship from.
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.