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

Days of the Month

A list of days of a month, accounting for the difference in months:

TOP(
  {
    1, 2, 3, 4, 5, 6, 7, 8, 9,
    10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
    20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
    30, 31
  },
  EOMONTH([Date], 0)
)
  1. {1, 2, 3, ..., 31} constructs a list of possible month day numbers.
  2. EOMONTH([Date], 0) identifies the number of the last day of the month specified by the Date (or DateTime) value in the Date column.
  3. TOP(..., ...) returns a list of only the day numbers from 1 to the last day of the given month.

See also: EOMONTH()

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()

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.

See Also

ANY(), INDEX()

Did this answer your question?