Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Krcmajster
Helper IV
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 __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

4 REPLIES 4
Anonymous
Not applicable

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!

@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
Did I answer your question? Mark my post as a solution!

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors