cancel
Showing results for
Did you mean:
Highlighted Continued Contributor

## Converting a Excel formula ino DAX

Hello all,

I have a question; I use the following excel formula to calculate the difference in KM between 2 lon lat points:

`=IFERROR(ACOS(COS(RADIANS(90-E2)) *COS(RADIANS(90-I2)) +SIN(RADIANS(90-E2)) *SIN(RADIANS(90-I2)) *COS(RADIANS(F2-J2))) *6371;0)`

(this formula is not mine but one I found on the internet) In the above formula note that:
E2 = LatPoint1
F2 = LngPoint1
I2 = LatPoin2
J2 = LngPoint1

I was wondering if it is possible to convert this formula to a DAX query, since it would be convenient to be able to do this in Power BI.

L.Meijdam

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Post Prodigy

## Re: Converting a Excel formula ino DAX

Try:

`Distance = IFERROR(ACOS(COS(RADIANS(90-[Lat1]))*COS(RADIANS(90-[lat2]))+SIN(RADIANS(90-[Lat1]))*SIN(RADIANS(90-[lat2]))*COS(RADIANS([Lon1]-[Lon2])))*6371,0)`
2 REPLIES 2
Highlighted Post Prodigy

## Re: Converting a Excel formula ino DAX

Try:

`Distance = IFERROR(ACOS(COS(RADIANS(90-[Lat1]))*COS(RADIANS(90-[lat2]))+SIN(RADIANS(90-[Lat1]))*SIN(RADIANS(90-[lat2]))*COS(RADIANS([Lon1]-[Lon2])))*6371,0)`
Highlighted Continued Contributor

## Re: Converting a Excel formula ino DAX

Hello @Omega,

Okay this was alot more straightforward than I expected but it works indeed.

Thanks

Best regards,

L.Meijdam

Announcements #### Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers! #### June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (1,110)