Get most recent records

[masterl1983]

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:55	D001	50	50
01.01.2018 18:55	D001	51	51
01.01.2018 16:22	D002	60	48
01.01.2018 18:55	D002	60	37

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

[v-lili6-msft]

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:

Best Regards,

Lin

[v-lili6-msft]

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 ]  )
        ))

Step 2:

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

Result:

Best Regards,

Lin

[neilmacmullen]

@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]

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