Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
prateekraina
Memorable Member
Memorable Member

Find no of trips using Latitude and Longitude

‎03-21-2018 10:46 PM

Hello,

 

I have a scenario wherein I need to find out no of trips made by a driver based on Latitude and Longitude data which I receive.

I have a fixed Source Address, whenever the car leaves that address and comes back, it means one complete trip.

 

Following is the sample data. Assume that my source address coordinates are Latitude - LA1 and Longitude - LO1

 

TimestampLatitudeLongitudeTrips
3/15/18 11:00 AMLA1LO1 
3/15/18 11:15 AMLA1LO11
3/15/18 11:30 AMLA2LO2
3/15/18 11:45 AMLA3LO3
3/15/18 12:00 PMLA1LO1
3/15/18 12:15 PMLA1LO1 
3/15/18 12:30 PMLA1LO12
3/15/18 12:45 PMLA2LO2
3/15/18 1:00 PMLA3LO3
3/15/18 1:15 PMLA4LO4
3/15/18 1:30 PMLA1LO1
3/15/18 1:45 PMLA1LO1 
3/15/18 2:00 PMLA1LO1 
3/15/18 2:15 PMLA1LO1 
3/15/18 2:30 PMLA1LO1 
3/15/18 2:45 PMLA1LO13
3/15/18 3:00 PMLA3LO3
3/15/18 3:15 PMLA3LO3
3/15/18 3:30 PMLA3LO3
3/15/18 3:45 PMLA1LO1
3/15/18 4:00 PMLA1LO1 
3/15/18 4:15 PMLA1LO1 

 

As per above data, no of trips is 3. Can anyone suggest how to achive this calculation?

 

Prateek Raina

Labels:
Message 1 of 12
1,652 Views
0
1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee
In response to prateekraina

‎03-22-2018 10:00 PM

HI @prateekraina

 

Please try this modified calculated column to allow for DriverID

 

Trips = 
VAR SourceLat = "LA1"
VAR SourceLng = "LO1"
VAR DriverID = 'Table1'[DriverID]
VAR NextTimeStamp = 
    MINX(
        FILTER(
            'Table1',
            'Table1'[Timestamp] > EARLIER('Table1'[Timestamp])
            && 'Table1'[DriverID] = DriverID
            ),
        'Table1'[Timestamp]
        )
VAR NextLat = MINX(FILTER('Table1',Table1[Timestamp] = NextTimeStamp && 'Table1'[DriverID] = DriverID),'Table1'[Latitude])
VAR NextLng = MINX(FILTER('Table1',Table1[Timestamp] = NextTimeStamp && 'Table1'[DriverID] = DriverID),'Table1'[Longitude])
VAR Result =  
    IF (
        'Table1'[Latitude] = SourceLat && 
        'Table1'[Longitude] = SourceLng && 
        'Table1'[Latitude] <> NextLat &&
        'Table1'[Longitude] <> NextLng && 
        NOT ISBLANK(NextLat) ,1 , BLANK())
        
RETURN Result

 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Message 5 of 12
1,771 Views
11 REPLIES 11

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All