Get values based on maximum timestamp

[Anonymous]

Hi everyone,

I have a direct query model with machine sensor data. Basically, there are machines (about 100) with data like incoming and outgoing bar values updated every 30 seconds. A typical table looks like this:

MachineNumberLocal	TagTimeStamp_EST	IncomingBar	OutgoingBar
10	1/21/2022 6:00	79.94944	77.60694885
11	1/21/2022 6:01	79.99097443	77.65860748
10	1/21/2022 6:02	80.2831955	77.54640198
11	1/21/2022 6:03	80.10600281	77.76474762

I want to idenity the latest timestamp for each machine along with it's associated incoming and outgoing bar values. So, the output should look like this since 10's latest time stamp is 6:02 and 11's is 6:03 and the associated bar values: 

10	1/21/2022 6:02	80.2831955	77.54640198
11	1/21/2022 6:03	80.10600281	77.76474762

Since it's direct query, I can't use the all rows expansion method that I've seen other people use. Is there a way to do this using Summarize, for example?

Thank you so much in advance!

[Anonymous]

@amitchandak 

I tried it and the measures behave a bit weirdly when used together. 

I brought in Machine Number, Incoming Bar and Outgoing Bar to the same table and it calculates the first measure just fine, but the second one does not show all values. For example, when icoming bar is added to the visual first, outgoing bar does not have all values. When outgoing bar is added to the table visual first (as in image below), incoming bar does not have all the values. 

Any idea why?

[Anonymous · Accepted solution]

Hi @Anonymous ,

I suggest you to create a measure , add this measure into visual level filter in your visual and set it to show items when value =1.

Then your visual will only show the data in latest timestamp for each machine number.

Measure Filter = 
VAR _LatestTimeStamp_EachMachine = MAXX(FILTER(ALL('Table'),'Table'[MachineNumberLocal] = MAX('Table'[MachineNumberLocal])),'Table'[TagTimeStamp_EST])
RETURN
IF(MAX('Table'[TagTimeStamp_EST]) = _LatestTimeStamp_EachMachine,1,0)

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@Anonymous , Create two meausre like

IncomingBar Measure =
VAR __id = MAX ('Table'[MachineNumberLocal] )
VAR __date = CALCULATE ( MAX('Table'[TagTimeStamp_EST] ), ALLSELECTED ('Table' ), 'Table'[MachineNumberLocal] = __id )
CALCULATE ( MAX ('Table'[IncomingBar] ), VALUES ('Table'[MachineNumberLocal] ),'Table'[MachineNumberLocal] = __id,'Table'[TagTimeStamp_EST] = __date )

OutgoingBar Measure =
VAR __id = MAX ('Table'[MachineNumberLocal] )
VAR __date = CALCULATE ( MAX('Table'[TagTimeStamp_EST] ), ALLSELECTED ('Table' ), 'Table'[MachineNumberLocal] = __id )
CALCULATE ( MAX ('Table'[OutgoingBar] ), VALUES ('Table'[MachineNumberLocal] ),'Table'[MachineNumberLocal] = __id,'Table'[TagTimeStamp_EST] = __date )

if needed use max of TagTimeStamp_EST in visual