how to find out if there was a meeting when the car was parked

[andrieskoo]

I have two tables Meetings  and CarLocations.
example Meeting table :

ID	START_DATE	END_DATE	START_LAT	START_LONG	START_TIMESTAMP
17057	29.01.2022 11:04	29.01.2022 15:46	53,58310384	19,55996569	1643450684
16255	14.01.2022 17:19	14.01.2022 20:50	53,61315626	20,41673756	1642177141
15823	03.01.2022 15:32	03.01.2022 19:02	54,16994362	19,40364511	1641220337

example CarLocations table:

START_LAT	START_LONG	PARKED_LAT	PARKED_LONG	DURING_TIME	START_TIME	PARKED_TIME
52,193501	21,057987	52,199035	21,044152	3G 24m 31S	25.11.2021 16:51	25.11.2021 13:27
52,199022	21,044111	52,199022	21,044111	2G 07m 01S	25.11.2021 19:00	25.11.2021 16:53
52,199022	21,044111	52,308966	20,929258	21m 44S	25.11.2021 19:49	25.11.2021 19:27

How can I find out if there was a meeting at the stop and which?

Can you help me please?

Thank you!

[amitchandak · Accepted solution]

@andrieskoo , a new column in table carlocations 

new column =

var _1  = countx(filter(Meeting, Meeting[START_LONG] >= carlocations[START_LONG] && Meeting[START_LONG] >= carlocations[PARKED_LONG] && Meeting[START_LAT] >= carlocations[START_LAT] && Meeting[START_LONG] >= carlocations[PARKED_LAT] && Meeting[START_DATE] <= carlocations[END_TIME] && Meeting[END_DATE] >= carlocations[START_TIME] ) ,Meeting[ID])

return _1

[v-cazheng-msft · Accepted solution]

Hi @andrieskoo 

You may try this Measure.

IsMeeting = 
VAR Table_ =
    SELECTCOLUMNS (
        Meetings,
        "ID",Meetings[ID],
        "START_DATE", Meetings[START_DATE]/*,
        "END_DATE", Meetings[END_DATE],
        "START_LAT_", Meetings[START_LAT],
        "START_LONG_", Meetings[START_LONG],
        "START_TIMESTAMP", Meetings[ START_TIMESTAMP]*/
    )
VAR midTable=
    CROSSJOIN ( Table_, CarLocations )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Meetings[ID] ) /*MAX(Meetings[ID])*/,
        FILTER (
            midTable,
            [START_DATE] > 'CarLocations'[PARKED_TIME]
                && [START_DATE] < 'CarLocations'[START_TIME]
        )
)

Then, the result should look like this.

Also, attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

[andrieskoo]

@amitchandak unfortunately there is no relation between the tables = (
I thought to search for each trip if there is a meeting that started after the end of the trip and ended before the next trip and then check if the GPS data is the same.
but I do not know how to do it

[amitchandak · Accepted solution]

@andrieskoo , a new column in table carlocations 

new column =

var _1  = countx(filter(Meeting, Meeting[START_LONG] >= carlocations[START_LONG] && Meeting[START_LONG] >= carlocations[PARKED_LONG] && Meeting[START_LAT] >= carlocations[START_LAT] && Meeting[START_LONG] >= carlocations[PARKED_LAT] && Meeting[START_DATE] <= carlocations[END_TIME] && Meeting[END_DATE] >= carlocations[START_TIME] ) ,Meeting[ID])

return _1