Returns an item from a list, as follows:
- The first item from a list if it is constructed (for example, using
LIST()
). - An arbitrary item if a list is a generated list (for example, using
FILTER()
orSELECT()
). - Blank if the list is empty.
Note: The order of the list cannot be guaranteed unless wrapped in SORT()
.
Sample usage
ANY(Students[Name])
returns an arbitrary value from the Name
column of the Students
table. Arbitrary because the order of values in the Students[Name]
column list isn't guaranteed unless wrapped in SORT()
. Equivalent to ANY(SELECT(Students[Name], TRUE))
. See also: SELECT()
ANY(LIST(1, 2, 3))
returns 1
(Number)
ANY({"Red", "Yellow", "Green"})
returns Red
(Text)
Column value
A single column value from any of a set of rows:
ANY(SELECT(Products[Price], ([Color] = "Orange")))
SELECT(Products[Price], ...)
returns values in thePrice
column from rows in theProducts
table that match the selection criteria.[Color] = "Orange"
limits the selection to only those rows with aColor
column value of exactlyOrange
.ANY(...)
returns one arbitrary value from the list of column values.
Equivalent to: LOOKUP("Orange", "Products", "Color", "Price")
Highest value in column
The highest product price:
ANY(TOP(SORT(Products[Price], TRUE), 1))
Products[Price]
retrieves the list of all values from thePrice
column of theProducts
table.SORT(..., TRUE)
orders the list of prices numerically in descending/high-to-low order (TRUE
).TOP(..., 1)
removes all but the first price in the sorted list.ANY(...)
returns the one remaining price from the top list.- Equivalent to
MAX(Products[Price])
.
Equivalent to MAX(Products[Price])
.
See also: MAX()
, SORT()
, TOP()
Preferred value
A mobile, office, or home phone number chosen from those that aren't blank:
ANY(
TOP(
(
LIST([Mobile Phone], [Office Phone], [Home Phone])
- LIST("")
),
1
)
)
LIST([Mobile Phone], [Office Phone], [Home Phone])
constructs a list of the three numbers.LIST(...) - LIST("")
removes any blank items from the list of numbers.TOP(..., 1)
removes all but the first from the list of non-blank numbers.ANY(...)
returns the only remaining non-blank number from the top list.
Equivalent to:
INDEX(
(
LIST([Mobile Phone], [Office Phone], [Home Phone])
- LIST("")
),
1
)
See also: LIST()
, INDEX()
, TOP()
Row with highest value in column
The row of the student with the highest GPA in Mr Sandwich's class:
ANY(
TOP(
ORDERBY(
FILTER("Students",
AND(
ISNOTBLANK([Teacher]),
([Teacher] = "Mr Sandwich")
)
),
[GPA], TRUE
),
1
)
)
FILTER("Students", ...)
returns a list of key values from theStudents
table that match a condition.AND(..., ...)
limits the filter to only those rows that match all of the given sub-conditions.ISNOTBLANK([Teacher])
requires theTeacher
column value not be blank.[Teacher] = "Mr Sandwich"
requires theTeacher
column value be exactlyMr Sandwich
.ORDERBY(..., [GPA], TRUE)
orders the filtered keys by the values of their correspondingGPA
column value in descending/high-to-low order (TRUE
), putting high GPAs first.TOP(..., 1)
removes all but the first item in the ordered list, leaving only the key of the row having the highest GPA.ANY(...)
returns the one remaining entry from the top list: the key of the row corresponding to the student with the highest GPA in Mr Sandwich's class.
Equivalent to:
MAXROW(
"Students", "GPA",
AND(
ISNOTBLANK([Teacher]),
([Teacher] = "Mr Sandwich")
)
)
See also: AND()
, FILTER()
, ISNOTBLANK()
, ORDERBY()
, MAXROW()
, TOP()
Common problems
ANY(1, 2, 3)
: the arguments are not in list form. To fix, wrap them in LIST()
to construct a list: ANY(LIST(1, 2, 3))
.
Syntax
ANY(list)
list
- List of any type.