topic Re: Haversine formula across tables in DAX Commands and Tips

Haversine formula across tables

rogerdea — Thu, 13 Jun 2019 13:12:43 GMT

I recently asked a question but think it was the wrong question so will take it back a step and see how you would do this.

I am trying to calculate the distance between two lat/long points at sea (so in nautical miles). The problem I have is that one set of lat/longs is in one table, and the other location is in another. The formula i would normally use with all the lat/longs n the same table is:

ACOS(COS(RADIANS(90-Lat1)) * COS((RADIANS(90-Lat2)) + SIN((RADIANS(90-Lat1)) * SIN((RADIANS(90-Lat2)) * COS(RADIANS(Lng1-Lng2))) * 3440.065)))

But i am not sure how to amend this so that it can read across two tables. Lat1 and Long1 reside in a table called "Station Locations" and Lat2 and Long2 reside in "LB_Incident" table.

Any idea on the best way to do this?

Thanks

Re: Haversine formula across tables

avanderschilden — Thu, 13 Jun 2019 17:58:13 GMT

Hi Roger,

Would you be able to provide a screenshot of the data model? I would like to see the relationship between those two tables you refer to.

Regards,

Adrian

Re: Haversine formula across tables

rogerdea — Fri, 14 Jun 2019 06:08:17 GMT

Re: Haversine formula across tables

avanderschilden — Fri, 14 Jun 2019 06:33:06 GMT

Hi Roger,

You can use the function RELATED to use a value from a table on the "one side" of the relationship.

This means you could create a calculation like;

COS(vwLB_Incident[Lat2]*RELATED('Station Locations'[Lat1]))

Let me know if this solves your challenge.

Regards,

Adrian

Re: Haversine formula across tables

rogerdea — Mon, 17 Jun 2019 07:49:51 GMT

Thanks very much, it's worked in as much as i can select the columns from the other tables in DAX (but only into a new column and not a new measure).

I'm still getting an error though. I noticed the last related statement is greyed out, is this the issue?

Re: Haversine formula across tables

avanderschilden — Mon, 17 Jun 2019 07:57:46 GMT

You are using RELATED on a column that already exist in the current table.
Close the RELATED funtion before you subtract the column that already is available without RELATED.

Re: Haversine formula across tables

rogerdea — Mon, 17 Jun 2019 08:12:46 GMT

Thanks a lot avanderschilden

I fixed that issue but still having trouble with the usual error message:

Re: Haversine formula across tables

avanderschilden — Mon, 17 Jun 2019 08:28:06 GMT

If you want to create a measure instead of a calculated column, you have to use an aggregator. If you for example use the AVERAGEX iterator you will have access to RELATED columns.

For example;

MEASURE =
AVERAGEX ( vwLB_Incident, COS ( RELATED ( ...

Re: Haversine formula across tables

rogerdea — Mon, 17 Jun 2019 09:27:06 GMT

Thanks again. I tried this as a measure but i am back to my original error. sorry for all the questions. any more ideas?

Re: Haversine formula across tables

avanderschilden — Mon, 17 Jun 2019 09:39:49 GMT

I created a measure that is quite similar (some "(" and ")" on different place) and don't get any errors;

Please review your code, because as you can see in my measure, RELATED works completely fine.