Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi, @abbas_uddin
Please try the below for creating a new table.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @abbas_uddin
Please try the below for creating a new table.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |