Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX RELATED


Share

The RELATED Function

The RELATED function gets a value from another table.

It follows a relationship from the current row to the matching row in the other table.

Syntax

RELATED(column)

The column is the column you want, in the other table.

RELATED follows a many-to-one relationship, from the "many" side to the "one" side.

Each Sales row has one product and one store, so from the Sales table you can get any column of Products or Stores:

RelationshipIn Sales, RELATED can get
Sales[ProductID] to Products[ProductID], many-to-oneProducts[Product], Products[Category], Products[UnitPrice], Products[UnitCost]
Sales[StoreID] to Stores[StoreID], many-to-oneStores[Store], Stores[Region]

RELATED works like VLOOKUP or XLOOKUP in Excel, but you do not have to say which columns to match. The relationship already knows.

This page uses the Poke Mart model described in DAX Introduction.


Create the Amount Column

In the previous chapter, Sales[Quantity] * Products[UnitPrice] failed. With RELATED, it works:

  1. Select Table view, and select the Sales table in the Data pane.
  2. On the Table tools tab, select New column. If you already have an Amount column with an error from the previous chapter, select that column in the Data pane instead.
  3. Enter the formula below in the formula bar, replacing what is there.
  4. Press Enter.

Example

Amount = Sales[Quantity] * RELATED(Products[UnitPrice])

For each order, RELATED follows the ProductID to the Products table and returns the unit price of that product.

The formula multiplies it by the quantity of the order.


Create the Cost Column

In the same way, add one more column to Sales, with the unit cost:

Example

Cost = Sales[Quantity] * RELATED(Products[UnitCost])

The first rows of the Sales table now look like this.

The Date and StoreID columns are not shown:

123OrderIDABCProductID123Quantity123Amount123Cost
1001P0611,200680
1002P01193,8002,090
1003P02137,8004,290
1004P089900405
1005P04195,7002,850
1006P09120095
...............

Check order 1002: product P01 is the Poke Ball, with a unit price of 200 and a unit cost of 110. 19 × 200 = 3,800, and 19 × 110 = 2,090.

The table has 2,740 rows, and every row now has an Amount and a Cost.

Numbers in this tutorial are shown with a thousands separator.



Check the Totals

Amount and Cost now hold the same values as the Sales and Cost columns in the flat file from First Report. Check it:

  1. Select Report view, click an empty spot on the canvas, and select the Table visual.
  2. In the Data pane, select Category in the Products table, then Amount and Cost in the Sales table.
CategorySum of AmountSum of Cost
Balls4,447,2002,477,560
Medicine4,413,6002,386,540
Status Healers556,000260,505
Total9,416,8005,124,605

The totals match the flat file: 9,416,800 in sales and 5,124,605 in costs.

Category is in the Products table, but it still splits up the Sales rows.

The relationship between the two tables takes care of that.

Power BI adds up the new columns automatically, just like it did with Sales in First Report.

In the next chapter, you replace these automatic sums with measures of your own.


When RELATED Does Not Work

  • No relationship: RELATED needs a relationship between the two tables. Without one, you get an error.
  • Wrong direction: RELATED only goes from the many side to the one side. In the Products table, RELATED(Sales[Quantity]) does not work, because one product has many Sales rows. For that direction, use RELATEDTABLE (see below).
  • No current row: RELATED needs a row context. It works in calculated columns. In a measure, it only works inside a function that goes through a table row by row, such as SUMX. You learn that in DAX SUMX.

RELATEDTABLE: The Other Direction

Seen from the Products table, each product has many Sales rows.

RELATEDTABLE returns all of them, as a table.

Syntax

RELATEDTABLE(table)

A column cannot hold a whole table in each row.

So you wrap RELATEDTABLE in a function that turns a table into one value.

COUNTROWS counts the rows of a table.

Add this calculated column to the Products table:

Example

Times Ordered = COUNTROWS(RELATEDTABLE(Sales))

For each product, RELATEDTABLE returns the Sales rows of that product, and COUNTROWS counts them:

ABCProductIDABCProductABCCategory123Times Ordered
P01Poke BallBalls512
P02Great BallBalls344
P03Ultra BallBalls179
P04PotionMedicine435
P05Super PotionMedicine367
P06Hyper PotionMedicine155
P07ReviveMedicine153
P08AntidoteStatus Healers251
P09Paralyze HealStatus Healers185
P10AwakeningStatus Healers159

The Poke Ball is ordered most often, and Revive least often.

The 10 numbers add up to 2,740, the number of rows in Sales.

Note: Why not name the column Order Count?

Later in this chapter, you create a measure called Order Count.

Power BI does not let you give a measure the same name as a column that already exists in the model.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.