Syntax

TOP(some-list, how-many)

Arguments

  • some-list (list of any type)
  • how-many (Number): The number of item values from some-list to be returned.

Return Value

A list containing the values of the initial how-many items of some-list. If how-many is greater than the number of items in some-list, the return list will only contain as many items as some-list. If how-many is less than 1, the return list will be empty.

Examples

TOP(LIST("Red", "Yellow", "Green"), 2) returns a list of 2 items: Red , Yellow

TOP({"Red", "Yellow", "Green"}, 4) returns a list of 3 items: Red , Yellow , Green

Lowest Values

The 5 earliest employee hire dates: TOP(SORT(Employees[Hire Date]), 5)

  1. Employees[Hire Date] retrieves the list of all values from the Hire Date column of the Employees table.
  2. SORT(...)  orders the list of dates chronologically in ascending/low-to-high order (the default sort order), putting the earliest dates at the beginning.
  3. TOP(..., 5) returns the first 5 values from the sorted list, the 5 earliest hire dates.

See also: SORT()

Rows with Highest Values

The rows of the 3 students with the highest GPAs in Mr Sandwich's class: TOP(ORDERBY(FILTER("Students", AND(ISNOTBLANK([Teacher]), ([Teacher] = "Mr Sandwich"))), [GPA], TRUE), 3)

  1. FILTER("Students", ...) returns a list of key values from the Students table that match a condition.
  2. AND(..., ...) limits the filter to only those rows that match all of the given sub-conditions.
  3. ISNOTBLANK([Teacher]) requires the Teacher column value not be blank.
  4. [Teacher] = "Mr Sandwich" requires the Teacher column value be exactly Mr Sandwich.
  5. ORDERBY(..., [GPA], TRUE) orders the filtered keys by the values of their corresponding GPA column value in descending/high-to-low order (TRUE), putting high GPAs first.
  6. TOP(..., 3) returns the first 3 items in the ordered list, the keys of the rows having the 3 highest GPAs.

See also: AND(), FILTER(), ISNOTBLANK(), ORDERBY()

See Also

ANY(), INDEX()

Did this answer your question?