Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Advocate I
Advocate I

Removing filters in a distance measure when tables have a common table in between

Hello all,


I'm trying to write a measure to calculate distance between two lat/long pairs. The measure works as expected when my tables are NOT related; however, when the relationships are set up as the rest of the report requires, I've been unable to figure out the correct way to implement ALL/REMOVEFILTERS/similar such that the measure still works when the relationships are active.


Here's the working measure:




Distance to Owner = 
var Lat1 = MIN(SystemUser[Employee Latitude])
var Lng1 = MIN(SystemUser[Employee Longitude])

var Lat2 = MIN(Account[Latitude])
var Lng2 = MIN(Account[Longitude])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 + 
    COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 12742 * ASIN((SQRT(A))) * 0.621371
return final





Here's the SystemUser table:

Employee IDEmployee LatitudeEmployee Longitude


Here's the Account table:

Account IDAccount LatitudeAccount Longitude


There is an intermediate table in between the above tables:

IntermediateIDOwner IDAccount ID



SystemUser[Employee ID] one-to-many IntermediateTable[Owner ID] , cross filter direction is single from SystemUser -- > IntermediateTable

Account[AccountID] one-to-many IntermediateTable[Account ID], cross filter direction is BOTH


The goal is to be able to calculate distance between Employees and Accounts, and I've been testing my revised measure in a matrix like this :

 Account AAccount B
Employee 1 |5 miles10 miles
Employee 2 |7 miles12 miles


As the measure is currently written, however, I only get the same vastly incorrect numbers (~6,300 miles) for all entries in the matrix. I expect that the solution to my issue correcting the measure to remove the filters, but I've been unable to figure it out thus far and I'm hoping someone with more experience with ALL/REMOVEFILTERS/etc. may have some insight.

Super User
Super User

@kstiegler , can you create a column?

If they are related, You can bring in a column using related or relatedtable in another table


refer 4 ways to copy data from one table to another



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors