Forum Discussion
Max Datetime by User with Coordinates
Dear Experts,
I have a table in which User-ID, Recorded Datetime and coordinates are showing. Now i want to show only the max of datetime in the Maps visuals with coordinates. the data below is like this:
| Consultant ID | Datetime | Start Time Latitude | Start Time Longitude |
| A-11 | 6/8/2021 10:59 | 25.2339843 | 55.3203666 |
| A-11 | 6/8/2021 13:08 | 25.2339555 | 55.3203741 |
| A-11 | 6/8/2021 13:34 | 25.2339689 | 55.3203736 |
| A-11 | 6/9/2021 7:35 | 25.2341114 | 55.3190147 |
| A-11 | 6/9/2021 10:28 | 25.2339452 | 55.3203874 |
| B-20 | 6/8/2021 10:17 | 25.30869782 | 55.40167385 |
| B-20 | 6/8/2021 14:07 | 25.308563 | 55.40197406 |
| B-20 | 6/8/2021 14:20 | 25.30852062 | 55.40203467 |
| B-20 | 6/8/2021 15:02 | 25.30855714 | 55.40198429 |
| B-20 | 6/8/2021 17:28 | 25.30853591 | 55.40198806 |
| B-20 | 6/8/2021 17:28 | 25.30855758 | 55.40196614 |
| C-43 | 6/9/2021 9:29 | 25.33449463 | 55.38996968 |
| C-43 | 6/9/2021 10:17 | 25.3338375 | 55.38976669 |
| A-220 | 6/7/2021 19:41 | 25.20010877 | 55.275296 |
| A-220 | 6/8/2021 10:54 | 25.200072 | 55.2753205 |
The result which i expect is UserID, Max of time, and coordinate as follows:
| USER ID | Date Time | Latitude | Longitude |
| A-11 | 6/9/2021 10:28 | 25.2339452 | 55.3203874 |
| B-20 | 6/8/2021 17:28 | 25.30855758 | 55.40196614 |
| C-43 | 6/9/2021 10:17 | 25.3338375 | 55.38976669 |
| A-220 | 6/8/2021 10:54 | 25.200072 | 55.2753205 |
how to achieve this. i Tried creating new table with Summarize and calculate max but doesnt seem to work for me or i am not doing it right,
kindly help
regards,
Abbas
Hi, abbas_uddin
Please try the below for creating a new table.
New Table =SUMMARIZECOLUMNS ('Table'[Consultant ID],'Table'[Datetime],'Table'[Latitude],'Table'[Longitude],FILTER ('Table','Table'[Datetime]= CALCULATE (MAX ( 'Table'[Datetime] ),ALLEXCEPT ( 'Table', 'Table'[Consultant ID] ))))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
2 Replies
abbas_uddin , Try to add a measure like the first one and try
cnt =
VAR __id = MAX ('Table'[USER ID ] )
VAR __date = CALCULATE ( MAX('Table'[Date Time] ), ALLSELECTED ('Table' ), 'Table'[USER ID] = __id )
CALCULATE ( count ('Table'[USER ID] ), VALUES ('Table'[USER ID] ),'Table'[USER ID] = __id,'Table'[Date Time] = __date )if needed have measure for long/lat
Longitude =
VAR __id = MAX ('Table'[USER ID ] )
VAR __date = CALCULATE ( MAX('Table'[Date Time] ), ALLSELECTED ('Table' ), 'Table'[USER ID] = __id )
CALCULATE ( max ('Table'[Longitude] ), VALUES ('Table'[USER ID] ),'Table'[USER ID] = __id,'Table'[Date Time] = __date )
Latitude =
VAR __id = MAX ('Table'[USER ID ] )
VAR __date = CALCULATE ( MAX('Table'[Date Time] ), ALLSELECTED ('Table' ), 'Table'[USER ID] = __id )
CALCULATE ( max ('Table'[Longitude] ), VALUES ('Table'[USER ID] ),'Table'[USER ID] = __id,'Table'[Date Time] = __date )Hi, abbas_uddin
Please try the below for creating a new table.
New Table =SUMMARIZECOLUMNS ('Table'[Consultant ID],'Table'[Datetime],'Table'[Latitude],'Table'[Longitude],FILTER ('Table','Table'[Datetime]= CALCULATE (MAX ( 'Table'[Datetime] ),ALLEXCEPT ( 'Table', 'Table'[Consultant ID] ))))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
