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 ...
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.