How to get dynamic latest location of machines?

[gautamakash8055]

I have a dataset having columns VIN, Timestamp, Latitude and Longitude. I need to create a vizualizaion in power bi where I could show the latest location of each vin with single dot. Also there should be a slicer of date on the page and if someone changes the date the latest location should change accordingly with respect to date.

[gautamakash8055 · Accepted solution]

Thanks @jpessoa8. I think this kind of solution I am looking for. What you have done is perfect can you also help in showing the latest location of all VIN rather than single vin at a time. Let say user is not selecting any specific vin from slicer so he could se 10 dots if 10 machines are there. Also the date slicer what you have created is from another table but in my case there are other visuals on same page which might get affected if we take date from other table.

VIN	Datetime	Lat	Lon
MG679915	2021-06-10T00:35:58.000Z	47.15984	-119.853
MG679908	2021-07-29T21:17:58.000Z	36.16232	-119.333
MG679904	2021-08-14T14:12:29.000Z	36.33719	-119.467
MG679904	2021-08-23T17:12:45.000Z	36.63368	-119.912
MG679904	2021-09-02T20:48:47.000Z	36.38712	-119.948
MG679915	2022-09-22T23:54:26.000Z	32.1245	-116.321
NG680512	2022-10-21T12:18:16.000Z	32.1423	-116.325
NG680513	2022-10-22T12:00:08.000Z	40.89916	-98.3852
NG680540	2022-10-25T10:52:53.000Z	47.15984	-119.853

[gautamakash8055 · Accepted solution]

Thanks @jpessoa8. I think this kind of solution I am looking for. What you have done is perfect can you also help in showing the latest location of all VIN rather than single vin at a time. Let say user is not selecting any specific vin from slicer so he could se 10 dots if 10 machines are there. Also the date slicer what you have created is from another table but in my case there are other visuals on same page which might get affected if we take date from other table.

VIN	Datetime	Lat	Lon
MG679915	2021-06-10T00:35:58.000Z	47.15984	-119.853
MG679908	2021-07-29T21:17:58.000Z	36.16232	-119.333
MG679904	2021-08-14T14:12:29.000Z	36.33719	-119.467
MG679904	2021-08-23T17:12:45.000Z	36.63368	-119.912
MG679904	2021-09-02T20:48:47.000Z	36.38712	-119.948
MG679915	2022-09-22T23:54:26.000Z	32.1245	-116.321
NG680512	2022-10-21T12:18:16.000Z	32.1423	-116.325
NG680513	2022-10-22T12:00:08.000Z	40.89916	-98.3852
NG680540	2022-10-25T10:52:53.000Z	47.15984	-119.853

[jpessoa8 · Accepted solution]

Hi @gautamakash8055 ,

If you add the VIN column to the Legend of the graph It will work like you want.

Regarding the issue of the Date coming from other table, I don't understand the issue but you could go 2 way:

1. Use in the slicer the Date field from the main table
2. Disable the interaction between the date slicer and the other visuals in the page (Format -> Edit Interactions -> Select the Visual -> Set the interactions with other visuals in the page

Hope this answer solves your problem!

If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa

[jpessoa8]

Hi @gautamakash8055,

Without a dataset it's hard to create a solution that will fit your needs entirely.

I created a dummy dataset like this which should be roughly what you have:

Since you need to grant the user the possibility to filter by date, the best option is to relate this table with a Calendar table but, for the relationship, you need to have the column has date only (otherwise the calendar table need to go to hour/minute/second level). To solve this, in the PowerQuery I split the TimeStamp into Date and TimeStamp columns:

With this I can create the relationship:

Now, for the measure to return only the last timestamp per VIN and even by Date, the calculation needs to check the last date for each VIN and afterwards get the last timestamp for that date. It can be something like this:

Last = 
var vMaxDate = CALCULATE(MAX('Table'[Date]),ALL('Table'[Lat],'Table'[Long]))
var vTimeStamp = CALCULATE(MAX('Table'[TimeStamp]),'Table'[Date]=vMaxDate)
return IF(not ISBLANK(vTimeStamp), vMaxDate + vTimeStamp)

The not ISBLANK() part is because the var vMaxDATE will return the same date for every Lat Long that a VIN was in, and then the measure shoul donly return a value when the vTimeStamp returns the last TimeStamp for the last date. I've tried to exemplify in this table the logic:

Now, applying the created measure to a Map Visual as a Visual Level filter filtered by "is not blank" you will be able to choose whatever VIN and Date and it will show only 1 dot on the map:

As you can see on the left side, the VIN "JTHKD5BH0E2196075" has values for 21/05/2021 but the slicer of date is only considering values until 22/03/2021 therefore shows 22/03/2021 06:00:00 as the last TimeStamp and it's corresponding Lat Long on the map.

Here is the PowerBI file I've used to implement this : https://we.tl/t-gE4G332pCA

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa