cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Calculation of distances

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 __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()

4 REPLIES 4
Anonymous
Not applicable

Hi @Krcmajster

Why not try to do it in power bi ?

Best Regards,
Kelly
Helper IV

@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
Not applicable

Hi @Krcmajster

Can you provide me some sample data and expected output?

Best Regards,
Kelly
Helper IV

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.