Forum Discussion
Get values based on maximum timestamp
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!
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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
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?
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
