DAX Formula

[Anonymous]

Hello everyone,

I need to check if the GPS coordinates (longitude and latitude) are the same on two tables (with a little variation/delta of +/- 0.0001).

Here is an example of my data from my table A :

Identifiant	PositionTimeStampp		Long		Lat		SameLongitude		SameLatitude
1	21/07/2022 14:50:00		47.307390		0.69521		Yes		Yes
1	20/07/2022 14:40:00		47,25620		0,09238		Yes		Yes
2	21/07/2022 13:50:00		47,28943		0,10107		No		No
2	19/07/2022 13:40:00		47,25620		0,09237		Yes		Yes

Here is an example of my data from my table B :

Identifiant		GPS coordinates		Longitude		Latitude
4		47.307390, 0.69522		47.307390		0.69522
5		47,25621, 0,09237		47,25621		0,09237

In these two tables:
- The first row of table A has (with variation) the same coordinates as the first row of table B.
- The second row and the fourth row of table A have (with the variation) the same coordinates as the second row of table B.
- However the third row of table A has no coordinates in common with table B

Do you have a solution or an idea ?

Best regards

[PaulDBrown · Accepted solution]

What Data Type are the Lat and Long fields? (text, decimal number?)

A) If the fields are type Decimal Number:

Check Long =
VAR _v1 =
    FORMAT ( MAX ( Table1[Long] ), "#0.00000" )
VAR _T =
    VALUES ( 'Table 2'[Longitude] )
VAR _Tplus =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[Longitude] ),
        "Longitude", 'Table 2'[Longitude] + 0.00001
    )
VAR _Tminus =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[Longitude] ),
        "Longitude", 'Table 2'[Longitude] - 0.00001
    )
VAR _UN =
    SELECTCOLUMNS (
        UNION ( _T, _Tplus, _Tminus ),
        "Text", FORMAT ( [Longitude], "#0.00000" )
    )
RETURN
    IF ( _v1 IN _UN, "Yes", "No" )

Check Lat =
VAR _V1 =
    FORMAT ( MAX ( Table1[Lat] ), "#0.00000" )
VAR _T =
    VALUES ( 'Table 2'[Latitude] )
VAR _Tplus =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[Latitude] ),
        "Latitude", 'Table 2'[Latitude] + 0.00001
    )
VAR _Tminus =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[Latitude] ),
        "Latitude", 'Table 2'[Latitude] - 0.00001
    )
VAR _UN =
    SELECTCOLUMNS (
        UNION ( _T, _Tplus, _Tminus ),
        "Text", FORMAT ( [Latitude], "#0.00000" )
    )
RETURN
    IF ( _V1 IN _UN, "Yes", "No" )

B) If the Lat/long Fields are Type Text:

Check Long txt =
VAR _v1 =
    MAX ( Table1[TXT LONG] )
VAR _T =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[TXT Long] ),
        "Longitude", VALUE ( 'Table 2'[TXT Long] )
    )
VAR _Tplus =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[TXT Long] ),
        "Longitude", VALUE ( 'Table 2'[TXT Long] ) + 0.00001
    )
VAR _Tminus =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[TXT Long] ),
        "Longitude", VALUE ( 'Table 2'[TXT Long] ) - 0.00001
    )
VAR _UN =
    SELECTCOLUMNS (
        UNION ( _T, _Tplus, _Tminus ),
        "Text", FORMAT ( [Longitude], "#0.00000" )
    )
RETURN
    IF ( _v1 IN _UN, "Yes", "No" )

Check Lat txt =
VAR _V1 =
    MAX ( Table1[TXT LAT] )
VAR _T =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[TXT Lat] ),
        "Latitude", VALUE ( 'Table 2'[TXT Lat] )
    )
VAR _Tplus =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[TXT Lat] ),
        "Latitude", VALUE ( 'Table 2'[TXT Lat] ) + 0.00001
    )
VAR _Tminus =
    SELECTCOLUMNS (
        VALUES ( 'Table 2'[TXT Lat] ),
        "Latitude", VALUE ( 'Table 2'[TXT Lat] ) - 0.00001
    )
VAR _UN =
    SELECTCOLUMNS (
        UNION ( _T, _Tplus, _Tminus ),
        "Text", FORMAT ( [Latitude], "#0.00000" )
    )
RETURN
    IF ( _V1 IN _UN, "Yes", "No" )

I've attached the sample PBIX

[Anonymous]

Hello Paul,

In my case, it was option B (my fields were of type Text)

Thank you for your answer, this seems correct for my need

Best regards