New Formula Please: LOOKUP(MaxRow(...), ...) Hibrid

Many times when I’m build an app, I need to know:

  • What was the last of something, and what was X value from that something.

In order to accomplish this now, I have to either:

  • build in an additional column (to hold the result of the MaxRow()), or
  • “double-up” formulas (which is not the most advisable thing, especially if you have large tables)

For Example:

Let’s say I have an Orders table, and I want to know the “Sales Person” that entered the last order. To accomplish this inside a single column, I would have to create a formula similar to this:

LOOKUP(MAXROW(Orders, CreationDateTime, [OrderID] = [_ThisRow].[OrderID]), Orders, OrderID, Order_SalesPerson)


It would be most excellent if a hybrid formula could be introduced that was super efficient and allowed for this dual-behavior.

  • Find the last (or first) of something and pull a value from it (other than the Key).

As always, thanks for considering! (^_^)

Yesiree-bob!

Perhaps a generalization of this request would simply be to allow dereferencing, (i.e. .[col] ) on any Ref-returning expression. So in this case we could do

MAXROW(…).[Order_SalesPerson]

This would also allow us to do it in this case:

ANY(FILTER(…)).[column]

etc…

6 Likes

Or another option would be to include an additional (optional) parameter in MINROW() MAXROW() formulas, allowing you to specify the column you wish to grab a value from.

MAXROW(Table, CreationDateTime, {criteria}, Column_I_Want)

I like this one, I think it’s a really clean solution :wink:

4 Likes