Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Formula

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
121/07/2022 14:50:00 47.307390 0.69521 Yes Yes
120/07/2022 14:40:00 47,25620 0,09238 Yes Yes
221/07/2022 13:50:00 47,28943  0,10107  No No
219/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

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    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

     

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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