RELATEDTABLE
Returns the rows of a table on the many side that match the current row on the one side.
RELATEDTABLE(<table>)Returns: A table.
What it does
RELATEDTABLE is the reverse of RELATED. Standing on a dimension row, such as one customer, it returns all the fact rows that match that row, such as that customer's sales lines. Use it to count or add up the child rows of a dimension row inside a calculated column, or inside an iterator over the dimension.
Syntax and arguments
- table: the many-side table, such as Sales. Only the relationship from the current row's table is followed.
Example
The number of sales lines for each customer, as a calculated column on the Customer table.
Sales Lines =
COUNTROWS ( RELATEDTABLE ( Sales ) )
For each customer row, RELATEDTABLE returns that customer's sales lines, and COUNTROWS counts them. A customer with no sales gets zero.
Example: a second use
The lifetime sales for each product, as a calculated column on the Product table.
Lifetime Sales =
SUMX (
RELATEDTABLE ( Sales ),
Sales[SalesAmount]
)
The iterator adds the sales amount for each of the product's lines. Because this is a calculated column, it covers all dates and ignores the report slicers. That makes it a fixed history, not a filtered total.
Things to watch
- Calculated columns are computed at refresh and do not respond to slicers. Inside a measure, use
CALCULATEwith the relationship instead. - It needs a row context on the one side, such as a calculated column or an iterator over the dimension.
- It returns many rows, so use an aggregator such as
COUNTROWSorSUMX. - Calculated columns that use it over large fact tables add memory and refresh time.
- A customer with no sales returns an empty table, not an error.
In business terms
Invoice counts per customer for credit reviews, time entries per client for a billing review, purchases per vendor for a spend review, and cleanup of a master file to find records with no activity.