DAX Long / Lat calculation not working

[HenryJS]

Hi all,

I input the below DAX to calculate distance from rows in CandPost table from Bicester Site table.

Distance From Compound =

var Lat1 = MIN('CandPost'[Latitude])

var Lng1 = MIN('CandPost'[Longitude])

var Lat2 = MIN('Bicester Site'[Latitude])

var Lng2 = MIN('Bicester Site'[Longitude])

---- Algorithm here -----

var P = DIVIDE( PI(), 180 )

var A = 0.5 - COS((Lat2-Lat1) * p)/2 +

COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2

var final = 12742 * ASIN((SQRT(A)))

return final

It returned the same distance for each row?

1. Bicester Site table

2. CandPost table

Thanks,

Henry

[v-lid-msft]

Hi @HenryJS ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[amitchandak]

When you create a column, the min, max will go across the column and give the same value. Is that is what you are trying? else you need to reconsider the logic or create a measure.

[HenryJS]

Hi @amitchandak 

I want to create a column in 'CandPost' which calculates the distance between each 'CandidateRef' from the longitude/latitude in 'Bicester Site' table.

This would be a different km value for each candidate ref as they are all in different locations.

Can you adivse? I'm stuck

Thanks,

Henry

[v-lid-msft]

Hi @HenryJS ,

We can try to create a calculated column using following dax to meet you requirement:

Distance From Compound =
VAR Lat1 = [Latitude]
VAR Lng1 = [Longitude]
VAR Lat2 =
    MIN ( 'Bicester Site'[Latitude] )
VAR Lng2 =
    MIN ( 'Bicester Site'[Longitude] ) ---- Algorithm here -----
VAR P =
    DIVIDE ( PI (), 180 )
VAR A =
    0.5
        - COS ( ( Lat2 - Lat1 ) * p ) / 2
        + COS ( Lat1 * p )
            * COS ( lat2 * P )
            * (
                1
                    - COS ( ( Lng2 - Lng1 ) * p )
            ) / 2
VAR final =
    12742 * ASIN ( ( SQRT ( A ) ) )
RETURN
    final

Best regards,