Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gautamakash8055
Regular Visitor

How to get dynamic latest location of machines?

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.

2 ACCEPTED SOLUTIONS
gautamakash8055
Regular Visitor

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.

gautamakash8055_0-1701359531013.png

VINDatetimeLatLon
MG6799152021-06-10T00:35:58.000Z47.15984-119.853
MG6799082021-07-29T21:17:58.000Z36.16232-119.333
MG6799042021-08-14T14:12:29.000Z36.33719-119.467
MG6799042021-08-23T17:12:45.000Z36.63368-119.912
MG6799042021-09-02T20:48:47.000Z36.38712-119.948
MG6799152022-09-22T23:54:26.000Z32.1245-116.321
NG6805122022-10-21T12:18:16.000Z32.1423-116.325
NG6805132022-10-22T12:00:08.000Z40.89916-98.3852
NG6805402022-10-25T10:52:53.000Z47.15984-119.853

View solution in original post

Hi @gautamakash8055 ,

 

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

jpessoa8_0-1701363697413.png

 

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

jpessoa8_1-1701363958211.png

 

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

View solution in original post

3 REPLIES 3
gautamakash8055
Regular Visitor

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.

gautamakash8055_0-1701359531013.png

VINDatetimeLatLon
MG6799152021-06-10T00:35:58.000Z47.15984-119.853
MG6799082021-07-29T21:17:58.000Z36.16232-119.333
MG6799042021-08-14T14:12:29.000Z36.33719-119.467
MG6799042021-08-23T17:12:45.000Z36.63368-119.912
MG6799042021-09-02T20:48:47.000Z36.38712-119.948
MG6799152022-09-22T23:54:26.000Z32.1245-116.321
NG6805122022-10-21T12:18:16.000Z32.1423-116.325
NG6805132022-10-22T12:00:08.000Z40.89916-98.3852
NG6805402022-10-25T10:52:53.000Z47.15984-119.853

Hi @gautamakash8055 ,

 

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

jpessoa8_0-1701363697413.png

 

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

jpessoa8_1-1701363958211.png

 

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
Continued Contributor
Continued Contributor

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:

 

jpessoa8_0-1701252224316.png

 

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:

jpessoa8_1-1701252367031.png

 

With this I can create the relationship:

jpessoa8_2-1701252387067.png

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:

 

jpessoa8_3-1701252911643.png

 

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:

 

jpessoa8_5-1701253280552.png

 

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

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors