Haversine formula across tables

[rogerdea]

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

[avanderschilden]

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

[rogerdea]

[avanderschilden]

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

[rogerdea]

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?

[avanderschilden]

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.

[rogerdea]

Thanks a lot @avanderschilden 

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

[avanderschilden]

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 ( ...

[rogerdea]

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

 

[avanderschilden]

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.