Calculation of distances

[Krcmajster]

Hi, 

I am using Haversine formula that works great if I have 2 tables. So they are not filtering each other

Distance (mil) = 
// calculated in KM, leter converted to MI
VAR __latSelected = SELECTEDVALUE(Locations[Latitude])
VAR __lonSelected = SELECTEDVALUE(Locations[Longtitude])
VAR __radius = 6371
VAR __multiplier = PI()/180
VAR __latDiff = (MIN(Locations2[Latitude])-__latSelected) * __multiplier
VAR __lonDiff = (MIN(Locations2[Longtitude])-__lonSelected) * __multiplier
VAR __formula1 = 
    SIN(__latDiff/2) * SIN(__latDiff/2) +
    COS(MIN(Locations2[Latitude]) * __multiplier) * COS(__latSelected * __multiplier) * 
    SIN(__lonDiff/2) * SIN(__lonDiff/2)
VAR __formula2 = 2 * ATAN(DIVIDE(SQRT(__formula1),SQRT(1-__formula1)))
VAR __distance = __radius * __formula2
RETURN __distance/0.621371192

But after transitioning to Azure I can't create calculated tables, so I need the same thing on a single table. Goal is to filter the map when a single property is selected from a table or visual to showcase everything in 20 miles radious, so basicaly:

If Distance (mil)<=20, True(), False()

I have already tried asking this but without success https://community.powerbi.com/t5/Desktop/Haversine-on-the-single-table-with-DAX/m-p/1066975#M497201

[Anonymous]

Hi @Krcmajster ，

Why not try to do it in power bi ?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Krcmajster]

@Anonymous I am trying to have the measure in power bi. But not sure how to iterate over sigle table without filtering everything. I tried ALL() but without success.

[Anonymous]

Hi @Krcmajster ，

Can you provide me some sample data and expected output?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Krcmajster]

@Anonymous I can't share the data but can give you an example. 

Location id        Lat                    Long

1                       47.4831427       19.0673242

2                       47.4831457       19.0673268

3                       44.4379269       26.0245983

If location 1 is selected, location 1 and 2 will pop on the map (or other visuals), 3 is not within 20 miles so it's excluded from further calculations. Same would be if location 2 is selected. If location 3 is selected, only location 3 will be present. 

Until now I could copy the table to have 2 tables to prevent filtering and use the formula I have mentioned to calculate the distance. I need to do the same, just with one table.