Solved: Re: Using Sort() Function

AdemarN · 08-27-2023 08:36 AM

Hi,

I'm using Top(Sort(Select())) in the expression below and expecting to retrieve the [Group Level] for last row it finds but it is giving me the first row. What am I missing?

TOP(SORT(select(Event Rider Details[Group Level], [_THISROW].[TransponderID]=[Transponder ID]),FALSE),1)

Thanks.

AdemarN

Ok I think I got it. @SkrOYC let me on to something so I did this...

I created a VC on the [Event Rider Details] table that returns a yes/no if the reocord is the Latest Record:

[vcLatest Record] = IF(MAXROW("Event Rider Details","Timestamp",([TransponderID]=[_THISROW].[TransponderID]))=[Row ID],"YES","NO")

Then on the other table I filtered on [vcLatest Record] = "YES":

top(select(Event Rider Details[Group Level],AND([_THISROW].[TransponderID]=[TransponderID],[vcLatest Record]="YES")),1)

We're good to go.

Thanks All.

View solution in original post

Suvrutt_Gurjar

Please try

TOP(SORT(select(Event Rider Details[Group Level], [_THISROW].[TransponderID]=[Transponder ID]), TRUE),1)

AdemarN

Tried it. Same result.

Thanks,

SkrOYC

@AdemarN wrote:

last row it finds but it is giving me the first row

What's your definition of "last row?"

AdemarN

The last row in the table for that condition on [TransponderID] (row highlighted in the screenshot). In the table there's are two entries for TransponderID "00001". I want to to result in the bottom entry. So, I want the field to result in [Group Level] "3", but it keeps resulting in "2".

But ultimately, once I get this figured out, I will want it to be the row with the newest [Timestamp].

Thanks.

SkrOYC

I think you will need to create a VC to first orderby the column TimeStamp to make sure that the list of values is orderer as you want and then filter this list again and take the value you need.

Also, remember that TOP() returns a list, not a value.

You will find many posts about this in the community under "Select and Orderby"

AdemarN

Not sure I understand how to use a Virtual Column to sort. Can you elaborate? Also, "List" rather than "Value". Can you suggest a different approach. I just want to return one text value, the one having the latest Timestamp, from a list of many rows....

AdemarN