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...
  • PaulDBrown's avatar
    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