Forum Discussion
Anonymous
3 years agoNot applicable
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...
- 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
PaulDBrown
3 years agoCommunity Champion
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
3 years agoNot applicable
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