cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kstiegler
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
145-90
246-91

 

Here's the Account table:

Account IDAccount LatitudeAccount Longitude
A45-90
B46-91

 

There is an intermediate table in between the above tables:

IntermediateIDOwner IDAccount ID
XYZ1A
ABC2B

 

Relationships:

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.

1 REPLY 1
amitchandak
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
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

 

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors