Top select orderby

[Simon1]

I’m using this expression to find the Top 4 rows of the Overstock table to show in another table.
TOP(
SELECT(Overstock[Location and Box Qty],([_THISROW].[Product]=[Product Code on Overstock]))
,4)

That works, but I need to order the list by a different column, Date Received, before showing the top 4.
When I add ORDERBY it doesn’t work, I think because the list I want isn’t the key of the Overstock table.
Is there a way I can do this?

[Steve · Accepted solution]

If your goal is to display some specific column–that is’t the key column–from those top four rows, you’ll have to take two steps:

1. Create a virtual column to receive the key column values of the top four rows.

2. Use a list dereference to get the desired column values from the list of the top four rows.

[Simon1]

I’m getting the top 4 rows of a column that isn’t the key using the expression:
TOP(
SELECT(Overstock[Location and Box Qty],([_THISROW].[Product]=[Product Code on Overstock]))
,4)

But I don’t want the top 4 rows, I need to reorder the data by another column, Date Received, and then get the top 4 rows when reordered

[SkrOYC]

Simon:

TOP(
SELECT(Overstock[Location and Box Qty],([_THISROW].[Product]=[Product Code on Overstock]))
,4)

TOP(
  ORDERBY(
    SELECT(
      Overstock[Location and Box Qty],
      ([_THISROW].[Product]=[Product Code on Overstock])
    ),
  [COLUMNFROMOVERSTOCKTHATWILLORDER],
  TRUE/FALSE
  ),
  4
)

help.appsheet.com

ORDERBY()

Sort row references

[Simon1]

When I try that it says Unable to find column Date Received, which is the [COLUMNFROMOVERSTOCKTHATWILLORDER]

If I change
SELECT(
Overstock[Location and Box Qty]
to
SELECT(
Overstock[DATE/TIME]
which is the key for the Overstock Table the expression works.
But that’s no good as I need to see [Location and Box Qty], not DATE/TIME

[Suvrutt_Gurjar]

Please try implementing what @Steve has suggested.

Assuming [DATE/TIME] is key of the table Oversock , please create the first VC called say [Top4Keys] with an expression something like

TOP(
ORDERBY(
SELECT(
Overstock[DATE/TIME],
([_THISROW].[Product]=[Product Code on Overstock])
),
[COLUMNFROMOVERSTOCKTHATWILLORDER],
TRUE/FALSE
),
4
)

Now please find the top [Location and Box QTY] in another VC called say [Top4QTYs] by the expression
[Top4Keys][Location and Box QTY]

[SkrOYC]

I deleted my post once I realized that it wouldn’t work.
You need two columns since to ORDERBY() keys are require.
So, you could have a list column with the keys and then you take the column you want using list dereference

help.appsheet.com

List Dereference

Dereference the items of a list in bulk to produce a list of the resulting values.

PS: I ignored @Steve’s post. The answer is clearly there

[Simon1]

Thanks. I didn’t realise a list could be referenced like that.
that’s useful to know

[Simon1]

Thanks Steve.
I didn’t realise a list could be referenced like that.