cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
masterl1983
Frequent Visitor

Get most recent records

Hi,

 

I have a table (actually a CosmosDB) where each document/record represents the location (Latitude/Longitude) of a device at a specific DateTime.

 

DateTime Device ID Longitude Latitude

01.01.2018 13:55D0015050
01.01.2018 18:55D0015151
01.01.2018 16:22D0026048
01.01.2018 18:55D0026037

 

On a Map in PowerBi I want to show a the latest known location of a Device. So for DeviceID D001 it would be the second row...

 

In my Datasource I do have like 20 distinct DeviceIDs each with 500+ location entries.

 

What would be the best approach?

 

Thanks in advance.

 

regards

Lutz

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi,@masterl1983

    Thank you for your reminding, I've improved my formula

new latest DateTime  = IF(CALCULATE(MAX(Table2[DateTime ]),FILTER (
            Table2,
            Table2[Device ID] = EARLIER ( Table2[Device ID] )
                && Table2[DateTime ]>=EARLIER ( Table2[DateTime ]  )
        ))=Table2[DateTime ],Table2[DateTime ])

Result:

6.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lili6-msft
Community Support
Community Support

hi,@masterl1983

     After my research, you can do these follow my steps like below:

Step1:

Add a latest datetime column

latest DateTime  = CALCULATE(MAX(Table2[DateTime ]),FILTER (
            Table2,
            Table2[Device ID] = EARLIER ( Table2[Device ID] )
                && Table2[DateTime ]< EARLIER ( Table2[DateTime ]  )
        ))

2.PNG

Step 2:

drag the field latest datetime into visual level filter to filter blank

3.PNG

 

Result:

4.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lili6-msft  The use of the blank column is a very useful tip! I think there is a small error in your logic though - it needs to be inverted to show the latest (rather than earliest) item?

 

The column should be 

 

latest DateTime  = CALCULATE(MAX(Table2[DateTime ]),FILTER (
            Table2,
            Table2[Device ID] = EARLIER ( Table2[Device ID] )
                && Table2[DateTime] > EARLIER ( Table2[DateTime ]  )
        ))

# Note the data comparison is _greater than_ because we are trying to find rows whose timestamp is large enough that no rows follow it

and then then the filter needs to filter _in_ the items that have a blank in this column.

neilmacmullen
Frequent Visitor

Hi Lutz, - it looks like we are facing similar problems - see my post at https://community.powerbi.com/t5/Desktop/Working-with-time-based-IOT-data-in-PowerBi-advice-needed/m...

 

The best advice I've seen for this kind of query is ...

 

1) Create a calculated column that tells you if this row is the latest for this particular sensor.  You do this by first FILTERing against rows where the id is equal to the outer id (using EARLIEST) then look for the MAX timestamp.  Once you have the timestamp, you can compare it to the current timestamp (using IF) 

2) In the visual use the "is latest" column to filter the results.

 

If step one seeems ridiculously complicated when you might think you could just write LATESTBY(deviceId,timestamp,latitude) , I share your pain! 

 

BTW - here's a worked solution https://community.powerbi.com/t5/DAX-Commands-and-Tips/Finding-the-most-recent-value/td-p/116838

 

 

And here is a useful video showing how you can create a query to do this... https://www.youtube.com/watch?v=5p5GYztKc9M&feature=youtu.be

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!