REF_ROWS()

Gather related rows from table or slice

Returns a list of key column values from rows in the specified table or slice in which the specified Ref-type column refers to this row (the row from which the REF_ROWS() expression is evaluated).

Answers the question: which rows in from-dataset refer to this row from ref-column?

Sample usage

REF_ROWS("Order Details", "Order ID") gives a list of rows from the Order Details table whose Order ID column value matches this row's key column value, where "this row" is the row from which the REF_ROWS() expression is evaluated.

REF_ROWS("Order Details", "Order ID") is functionally equivalent to each of the following:

  • FILTER("Order Details", ([_THISROW] = [Order ID]))
  • SELECT(Order Details[Order Details ID], ([_THISROW] = [Order ID])).

Syntax

REF_ROWS("from-dataset", "ref-column")

  • from-dataset - Name of the table or slice (the "data set") in which related rows are to be found.
  • ref-column - Name of a column in the data set named by from-dataset that contains Ref-type values.

Note: Both arguments must be raw text values (quoted or unquoted) that identify an existing table or slice (from-dataset) and column (ref-column); they may not be expressions.

Magical Behavior

If a column's App formula consists exclusively of a simple REF_ROWS() expression, a row added from the resulting list's inline view will automatically be populated with a reference back to the row of the REF_ROWS() expression, creating a child-parent relationship. The parent reference will be placed in the column named by the ref-column argument of the REF_ROWS() expression.

See also

FILTER()

LOOKUP()

MAXROW()

MINROW()

ORDERBY()

SELECT()

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
false