All lessons

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.

Product[UnitPrice] holds the 2024 list price, while Sales[UnitPrice] holds what was actually charged. Compare them:

Try it: write the DAX

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.

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.

Try it: write the DAX

Add a calculated column Sales[Region] with the store's region, then the query counts lines per region.

Press Run to see the result.

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

  1. Write a Sales calculated column Line Cost = Quantity × the product’s UnitCost from Product.
  2. 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.)
  3. Why does RELATED fail in a plain measure?
Answers
  1. Sales[Line Cost] = Sales[Quantity] * RELATED(Product[UnitCost]).
  2. Product[Stores] = CALCULATE(DISTINCTCOUNT(Sales[StoreKey])): CALCULATE turns the current product row into a filter on Sales.
  3. A measure has no current row, so there’s no row to follow the relationship from.

Check yourself

1Where can RELATED(Product[Category]) be used?
2Which direction does RELATED go?
3What does RELATEDTABLE(Sales) return in a Product calculated column?

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.