Slice and Sort lookup fields

2

Hi. Since First() and Last() exist, for a very contrived, bad, hacky attempt at this, I tried creating nested formulas that exclude the last/first from inner to outer layers, but unfortunately, I was unable to come up with a single-formula hack, since I got the error: Ouch, we can't calculate Count, Sum, Join, Avg, Min, Max, First or Last inside a Filter. Please create two Formulas instead.

So, only in very specific and urgent cases (more of a proof of concept that anything else), you can create n+1 columns to get the top/bottom-n by any sort, as shown in my example of getting the top 3 Tasks by Priority in a Projects table.

Formula Column: Task #1

Sorts all Tasks by Priority and takes the last one, which is the top-priority Task.

Tasks.Sort(Priority).Last()

Formula Column: Task #2

Sorts by Priority but excludes Task #1 by Public Id, then takes the last one, which is the 2nd-priority Task.

Tasks.Filter(
	[Public Id] != [This Project].[Task #1].[Public Id]
).Sort(Priority).Last()

Formula Column: Task #3

Sorts by Priority but excludes Task #1 and Task #2 by Public Id, then takes the last one, which is the 3rd-priority Task.

Tasks.Filter(
	[Public Id] != [This Project].[Task #1].[Public Id] and
	[Public Id] != [This Project].[Task #2].[Public Id]
).Sort(Priority)
.Last()

Formula Column: Top-3 Tasks A-Z

Filters for the top 3 Tasks, using Task #1, #2 and #3 by Public Id and sorts by Priority. See IMPORTANT NOTE below.

Tasks.Filter(
	[Public Id] = [This Project].[Task #1].[Public Id] or
	[Public Id] = [This Project].[Task #2].[Public Id] or
	[Public Id] = [This Project].[Task #3].[Public Id]
).Sort(Priority)

The sorting is not respected by the collection output, but only for aggregations such as Join(), as discussed in my message quoted below.