Forum Discussion

masterl1983's avatar
masterl1983
Frequent Visitor
8 years ago

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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

  • neilmacmullen's avatar
    neilmacmullen
    Frequent Visitor
    8 years ago

    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-p/512849/highlight/true#M239668

     

    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

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      neilmacmullen
      Frequent Visitor
      8 years ago

      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.