Power BI DAX RELATED
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:
| Relationship | In Sales, RELATED can get |
|---|---|
| Sales[ProductID] to Products[ProductID], many-to-one | Products[Product], Products[Category], Products[UnitPrice], Products[UnitCost] |
| Sales[StoreID] to Stores[StoreID], many-to-one | Stores[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:
- Select Table view, and select the Sales table in the Data pane.
- 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.
- Enter the formula below in the formula bar, replacing what is there.
- 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:
| 123OrderID | ABCProductID | 123Quantity | 123Amount | 123Cost |
|---|---|---|---|---|
| 1001 | P06 | 1 | 1,200 | 680 |
| 1002 | P01 | 19 | 3,800 | 2,090 |
| 1003 | P02 | 13 | 7,800 | 4,290 |
| 1004 | P08 | 9 | 900 | 405 |
| 1005 | P04 | 19 | 5,700 | 2,850 |
| 1006 | P09 | 1 | 200 | 95 |
| ... | ... | ... | ... | ... |
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:
- Select Report view, click an empty spot on the canvas, and select the Table visual.
- In the Data pane, select Category in the Products table, then Amount and Cost in the Sales table.
| Category | Sum of Amount | Sum of Cost |
|---|---|---|
| Balls | 4,447,200 | 2,477,560 |
| Medicine | 4,413,600 | 2,386,540 |
| Status Healers | 556,000 | 260,505 |
| Total | 9,416,800 | 5,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:
| ABCProductID | ABCProduct | ABCCategory | 123Times Ordered |
|---|---|---|---|
| P01 | Poke Ball | Balls | 512 |
| P02 | Great Ball | Balls | 344 |
| P03 | Ultra Ball | Balls | 179 |
| P04 | Potion | Medicine | 435 |
| P05 | Super Potion | Medicine | 367 |
| P06 | Hyper Potion | Medicine | 155 |
| P07 | Revive | Medicine | 153 |
| P08 | Antidote | Status Healers | 251 |
| P09 | Paralyze Heal | Status Healers | 185 |
| P10 | Awakening | Status Healers | 159 |
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.