Use of only function with 'Max' in set expression

[#1]

I have the following data tables:

Data:

LOAD * INLINE [

EventID, Status, Value

52, A, 10

53, E, 20

54, A, 30

];

Temp:

LOAD * INLINE [

ID, EventID

37, 52

37, 53

41, 54

41, 55

41, 56

41, 57

];

Requirement:

In straight table I have to display two columns 'ID' and 'Status'. There should be single row for each ID; meaning here, there should be distinct ID's in straight table. If there are multiple values for same ID, then it's 'Status' value in our straight table column should be of max of 'EventID'.

My Expression in straight table is :

if(count(DISTINCT [Status])>1,Only({<[EventID]={$(=max([EventID]))}>}[Status]),[Status])

For example:

The output I'm expecting in Straight table is : 

ID      Status

37          E

41          A

But its giving below output:

ID      Status

37          -

41          A

'E' is expected in status column instead of null i.e.' - '.

I'm not getting the correct output. I used 'Aggr' function with max, but did'nt work. Also to mention, use of maxstring function is not expected here. I want help for this issue.

Can you suggest expression similar to this.

[jagan]

Hi,

Why there is no status for 55, 56 and 57?  if there are values for that then use

=FirstSortedValue(Status, -EventID)

Regards,

Jagan.

[tresesco]

And if you have to take nulls into consideration and avoid it, try like:

FirstSortedValue({<Status={'*'}>}Status,-EventID)

PFA

[#4]

Hi All,

The expression suggested by you is not working for me.

For ID '37', its still displaying '-' instead of 'E' value in 'Status' column. !

[tresesco · Accepted solution]

A distinct would be required. Try like:

=FirstSortedValue({<[Patient Status]={'*'}>} Distinct [Patient Status], -[SS_Event_EP_ID])

[#6]

Thanks, It worked.

I appreciate your help!!