How to get the latest record for each Key?

[MatheusRuiz]

Hello, everyone. Hope someone can help me on that.

This is my example table:

I want to create a table visualization that shows only the last record for each Vehicle, and only if in this last record the value in the Type column is "ARRIVING".

I created this desired table in excel manually, but I need it automated in Power BI due to the large database. 

This is the view I need for the above example:

Note that I have 7 different vehicles in the original table and in the visualization I have only 2.

Thanks in advance!

[rsbin]

@MatheusRuiz ,

Add your table visual into your canvas.

From your Field List, drag Vehicle and Location into the Values well. 

Drag Date, but select "Latest" from the dropdown.

Then drag Type into the "Filters on this Visual" pane.  Check "ARRIVING".

This should get you what you are after.  If not, please post a small sample of your data as a table (not an image).

Regards,

[MatheusRuiz]

@rsbin,

I did as you said:

- created a table visual.

- dragged Vehicle into the Columns (instead of Values).

- then dragged Location and Date into the Columns. 

- selected Latest Date. But here is the problem: when I select that, the visual shows the latest record for each Vehicle+Location instead of the latest record for each Vehicle. 

I could not insert my table here, but see what I am saying:

Note that I have two rows for the vehicles FHC and UBZ (the data is sorted by Date).

I thought that excluding Location from the Columns I would get what I want. But when I do that, the table search for the latest "ARRIVING" record for each vehicle (returning 5 records, and in this example should be only 2 as I presented in the post).

I want the latest record for each vehicle and then I want to check if it is "ARRIVING"...

I am thinking that would be necessary to use a programming language, what do you say?

Thx!

[rsbin · Accepted solution]

@MatheusRuiz ,

Apologies for misunderstanding your original post.
I'm hoping this New Table will work for you:

LastArrivalTable = SUMMARIZE(
                        FILTER( Vehicle,
                                Vehicle[Date]
                                = CALCULATE (MAX ( Vehicle[Date] ),
                                   ALLEXCEPT ( Vehicle, Vehicle[Vehicle] ))),
                    Vehicle[Vehicle],
                    Vehicle[Date],
                    Vehicle[Location],
                    Vehicle[Type] )

This table will give you both ARRIVING and LEAVING.  You can use the Filter Pane to filter out LEAVING.  Pretty sure there should be a way to add this filter into the formula above, I just wasn't able to figure it out before I had to move on to some other work.

Best Regards,