Comparing latitude and longitude in a table against different table of latitude and longitudes

[gnotcirdec]

Hello.

I'm trying to compare data points in different tables to see if they are within each other. If tableA latitude and longitude are inside any of the points inside fence indicated by tableB, "isInside" column will return 1, if not 0. I'm looking to do this all in Power Query

Here is an example of tableA:

Device	Latitude	Longitude	isInside
A103	33.77519	-118.11782	0
A104	34.01295	-118.27539	1
A105	33.71391	-118.26318	0

Here is tableB (fence):

TopLatitude	TopLongitude	BottomLatitude	BottomLongitude
34.02513433	-118.2916831	34.01119056	-118.2682514
34.03056715	-118.2555056	34.02529439	-118.2521689

Any help would be appreciated!

Thank you.

[v-jingzhang · Accepted solution]

Hi @gnotcirdec 

Here is a DAX method. You can create a new column with below code.

Is Inside = 
VAR _table =
    ADDCOLUMNS (
        TableB,
        "IsInsideFlag",
            IF (
                TableA[Latitude] >= TableB[BottomLatitude]
                    && TableA[Latitude] <= TableB[TopLatitude]
                    && TableA[Longitude] >= TableB[TopLongitude]
                    && TableA[Longitude] <= TableB[BottomLongitude],
                1,
                0
            )
    )
RETURN
    IF ( COUNTROWS ( FILTER ( _table, [IsInsideFlag] = 1 ) ) >= 1, 1, 0 )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @gnotcirdec 

Here is a DAX method. You can create a new column with below code.

Is Inside = 
VAR _table =
    ADDCOLUMNS (
        TableB,
        "IsInsideFlag",
            IF (
                TableA[Latitude] >= TableB[BottomLatitude]
                    && TableA[Latitude] <= TableB[TopLatitude]
                    && TableA[Longitude] >= TableB[TopLongitude]
                    && TableA[Longitude] <= TableB[BottomLongitude],
                1,
                0
            )
    )
RETURN
    IF ( COUNTROWS ( FILTER ( _table, [IsInsideFlag] = 1 ) ) >= 1, 1, 0 )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[gnotcirdec]

Hi @v-jingzhang ,

Is there a way to modify the solution to include Text as the result?

For example:

Any help would be appreciated!

Thanks,

Cedric

[AlexisOlson · Accepted solution]

Try changing the last line of [Is Inside] to

MINX ( FILTER ( _table, [IsInsideFlag] = 1 ), [Location] )

[gnotcirdec]

Thank you! This works just as well.