Slice where the latest value in one column for each unique value in another column is "arrived"

[pete3]

TL;DR: How do I make a slice that only shows rows where the last entry for a given person is "arrived" and there is no "departed" entry after the "arrived" entry?

Setup

Imagine I have an app that shows "who's at the party right now". I have one table that looks something like this (called Activity):

ID	Time	Person	Interaction type
9B2734A	7pm	John	Arrived
3C5345A	7:30pm	Suzy	Arrived
3D5647A	8pm	John	Departed
0F8098A	8:30pm	Ralph	Arrived

I'm trying to make a slice that will show who's at the party, something like this:

Person

Suzy

Ralph

At some point I also want to figure out how to show how long they've been at the party, but for now I'm just trying to make the slice that shows only who's there.

What I've tried

I've got a formula working that will show all the rows which say "arrived", but how do I exclude the rows which also say "departed" for a given person?

[Interaction type]="Arrived"

That works, but still shows John who should not show up there because he has departed. I've tried some other stuff involving MAXROW, but I'm having a lot of trouble figuring out how to get that to work. 

[WillowMobileSys]

Based on your title, you are creating a Slice and I assume you are using the criteria 

[Interaction type]="Arrived"

If so, then the Slice will include ONLY the rows that are "Arrived".  You then need to make sure you are using THAT Slice as the datasource in your views.   

[pete3]

Thanks for your reply.  I have a slice set up, and a view created for that slice, however the view shows all that have "arrived" (3 people), but one of them has "departed" too (John), so I'm hoping to figure out a way to show on the view of that slice that only Ralph and Suzy are still there (since there are no "Activity" table entries with "departed" for those two after the rows which have "arrived" for them.)

[JMPeterson]

You might be able to set up a virtual column using the MAXROW() function to only look at the most recent Interaction type for each individual person. Then adjust your slice to only show rows where that virtual column is equal to "Arrived". Or do it all in the slice itself.

[pete3]

One thing that's really tripping me up is that if I use MAXROW or FILTER or SELECT (or a few other things I've tried, probably without much intelligence) is that I get a message like this:

The filter condition '=MAXROW("Activity","Time",[Interaction type]="Arrived")' of table slice 'Party' must return true or false

I clearly don't get how slices work, because I feel like it should return a list of rows where those conditions are true. But then again my expression is probably off.

[Kusadi]

[time]=max(table name[time],[interaction type]="arrived")

[pete3]

Hmm, when I put this in for the slice:

[Time]=MAX(Activity[Time],[Interaction type]="Arrived")

I get this error:

MAX function is used incorrectly

[JMPeterson]

MAXROW() is going to return the key column, so you will need to do something like

IN([ID],MAXROW("Activity","Time",AND([_THISROW].[Person]=[Person],[Interaction type]="Arrived")))

That should give you the list of most recent rows for each unique person that has the interaction type "Arrived".

[pete3]

When I plug that in, it tells me Parameter 2 of function IN is of the wrong type. Which makes me this the MAXROW isn't producing a list (the type that In is expecting for the second parameter)?

[JMPeterson]

Can you try changing "Time" to "_RowNumber" instead? The MAXROW function is looking for a numeric data type in the second field, and your "Time" field may be formatted as text.

[pete3]

Still getting an error:

IN([ID],MAXROW("Activity","_RowNumber",AND([_THISROW].[Person]=[Person],[Interaction type]="Arrived")))

Gives the error: Parameter 2 of function IN is of the wrong type

[JMPeterson]

Well, it looks like I had it backwards. I tested the following with success;

IN(MAXROW("Activity","_RowNumber",AND([_THISROW].[Person]=[Person],[Interaction type]="Arrived")),Activity[ID])

 Apologies for not verifying earlier.

[pete3]

That expression validates, but it doesn't return a list of only the people who are still at the party. I'll post some screenshots (note that I changed "ID" to "Interaction ID" and "Time" to "Log time" because I think that will help me keep things straight and I want to avoid errors using "Time".)

[JMPeterson · Accepted solution]

That looks like it should work, but I am obviously missing something. Alternative is to make a virtual column on your Activity table as such;

IF(
[_THISROW].[id]=MAXROW("Activity","_RowNumber",[_THISROW].[Person]=[Person]),
TRUE,
FALSE)

This simply flags the most recent row for each unique person.

Then have your slice filter by that virtual column and the interaction type you want to list.

AND(
[VC_MAXROW]=TRUE,
[Interaction type]="Arrived"
)

[pete3]

That did it; thanks so much for the help!