Downloads
DAXRelationships and lookupIntermediate

RELATEDTABLE

Returns the rows of a table on the many side that match the current row on the one side.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals