SELECT() gathers a list of values in a single column from selected rows of a data set.
SELECT(Students[First Name], TRUE)
A list of first names (possibly with duplicates) of all students. Equivalent to
SELECT(Students[First Name], ([Class of] = "2020"), FALSE)
A list of first names (possibly with duplicates) of the students of the class of 2020. Equivalent to
SELECT(Students[First Name], ([Class of] = "2020")).
SELECT(Students[First Name], ([Class of] = "2020"), TRUE)
A list of distinct first names (duplicates omitted) of the students of the class of 2020.
SELECT(Orders[Order ID], ([Customer] = [_THISROW].[Customer]))
returns the Order ID column values (the row keys) for rows in the Orders data set in which the Customers column value is equal to the Customers column value of the current form (i.e., orders for this customer). Equivalent to
FILTER("Orders", ([Customer] = [_THISROW].[Customer])). See also: FILTER().
SELECT(Products[Name], ([Price] < 100), TRUE)
returns the distinct names of products priced less than $100.
SELECT() can be used to link references. For example, imagine that we want to display our top purchasers of each product. In our Products table, we can create a virtual column called "OrdersByQuantity" with Type "List" and Element Type "Ref", with a formula like this:
ORDERBY(SELECT(Orders[Order ID], [Quantity]))
and then create a new virtual column of type list with the formula:
which "dereferences" each row of OrdersByQuantity back to the Orders table, allowing us to then access the PurchaserName.
SELECT( from-dataset-column , select-row? [ , distinct-only? ] )
from-dataset-column : The specification of the table or slice (the "data set") to search and the column from which values are to be gathered, in the form: dataset-name[column-name]. For example,
Orders[Order ID]. Although identical in appearance to a column list expression, this argument is not an expression.
- select-row? (Yes/No expression): An expression, evaluated for each row of the data set, that returns TRUE or FALSE indicating whether the column value from the row should be included (TRUE) or excluded (FALSE) in the results.
- distinct-only? (Yes/No): FALSE to indicate the results list should include all values found in selected rows, or TRUE to indicate duplicate values should be omitted. If not given, FALSE is assumed.
A list of values from the column of selected rows in the data set.
Within the second argument, the select-row? expression, any column references are interpreted from the perspective of the data set being searched, not that of the data set from which the expression is run. In order to reference columns from the current row, you must dereference _THISROW.
For example, consider this attempt from an order row to get the item descriptions from the order detail rows:
SELECT(Order Details[Description], ([Order ID] = [Order ID]), TRUE)
The goal is to select rows from the Order Details data set with an Order ID column value that matches this order's own ID. But within the select-row? expression (
([Order ID] = [Order ID])), both column references refer to the Order Details row being examined. As written, the expression will always be TRUE.
To reference a column of the current row, dereference _THISROW to get the desired column:
SELECT(Order Details[Description], ([Order ID] = [_THISROW].[Order ID]), TRUE)