Forum Discussion

MatheusRuiz's avatar
MatheusRuiz
Frequent Visitor
4 years ago
Solved

How to get the latest record for each Key?

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...
Need Help
Tutorial Requests
  • rsbin's avatar
    rsbin
    4 years ago

    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,

MatheusRuiz's avatar
MatheusRuiz
Frequent Visitor
4 years ago

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's avatar
rsbin
Icon for Community Champion rankCommunity Champion
4 years ago

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,