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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.