Max Datetime by User with Coordinates

[abbas_uddin]

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

[Jihwan_Kim · Accepted solution]

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

[amitchandak]

@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 )