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,

rsbin's avatar
rsbin
Icon for Community Champion rankCommunity Champion
4 years ago

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'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,