topic Removing filters in a distance measure when tables have a common table in between in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Removing-filters-in-a-distance-measure-when-tables-have-a-common/m-p/2544698#M71788
<P>Hello all,</P><P> </P><P>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.</P><P> </P><P>Here's the working measure:</P><P> </P><P> </P><P> </P><LI-CODE lang="markup">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</LI-CODE><P> </P><P> </P><P> </P><P> </P><P>Here's the SystemUser table:</P><TABLE border="1"><TBODY><TR><TD>Employee ID</TD><TD>Employee Latitude</TD><TD>Employee Longitude</TD></TR><TR><TD>1</TD><TD>45</TD><TD>-90</TD></TR><TR><TD>2</TD><TD>46</TD><TD>-91</TD></TR></TBODY></TABLE><P> </P><P>Here's the Account table:</P><TABLE border="1"><TBODY><TR><TD>Account ID</TD><TD>Account Latitude</TD><TD>Account Longitude</TD></TR><TR><TD>A</TD><TD>45</TD><TD>-90</TD></TR><TR><TD>B</TD><TD>46</TD><TD>-91</TD></TR></TBODY></TABLE><P> </P><P>There is an intermediate table in between the above tables:</P><TABLE border="1"><TBODY><TR><TD>IntermediateID</TD><TD>Owner ID</TD><TD>Account ID</TD></TR><TR><TD>XYZ</TD><TD>1</TD><TD>A</TD></TR><TR><TD>ABC</TD><TD>2</TD><TD>B</TD></TR></TBODY></TABLE><P> </P><P>Relationships:</P><P>SystemUser[Employee ID] one-to-many IntermediateTable[Owner ID] , cross filter direction is single from SystemUser -- > IntermediateTable</P><P>Account[AccountID] one-to-many IntermediateTable[Account ID], cross filter direction is BOTH</P><P> </P><P>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 :</P><TABLE border="1"><TBODY><TR><TD> </TD><TD>Account A</TD><TD>Account B</TD></TR><TR><TD>Employee 1 |</TD><TD>5 miles</TD><TD>10 miles</TD></TR><TR><TD>Employee 2 |</TD><TD>7 miles</TD><TD>12 miles</TD></TR></TBODY></TABLE><P> </P><P>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.</P>Fri, 27 May 2022 20:22:01 GMTkstiegler2022-05-27T20:22:01ZRemoving filters in a distance measure when tables have a common table in between
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Removing-filters-in-a-distance-measure-when-tables-have-a-common/m-p/2544698#M71788
<P>Hello all,</P><P> </P><P>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.</P><P> </P><P>Here's the working measure:</P><P> </P><P> </P><P> </P><LI-CODE lang="markup">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</LI-CODE><P> </P><P> </P><P> </P><P> </P><P>Here's the SystemUser table:</P><TABLE border="1"><TBODY><TR><TD>Employee ID</TD><TD>Employee Latitude</TD><TD>Employee Longitude</TD></TR><TR><TD>1</TD><TD>45</TD><TD>-90</TD></TR><TR><TD>2</TD><TD>46</TD><TD>-91</TD></TR></TBODY></TABLE><P> </P><P>Here's the Account table:</P><TABLE border="1"><TBODY><TR><TD>Account ID</TD><TD>Account Latitude</TD><TD>Account Longitude</TD></TR><TR><TD>A</TD><TD>45</TD><TD>-90</TD></TR><TR><TD>B</TD><TD>46</TD><TD>-91</TD></TR></TBODY></TABLE><P> </P><P>There is an intermediate table in between the above tables:</P><TABLE border="1"><TBODY><TR><TD>IntermediateID</TD><TD>Owner ID</TD><TD>Account ID</TD></TR><TR><TD>XYZ</TD><TD>1</TD><TD>A</TD></TR><TR><TD>ABC</TD><TD>2</TD><TD>B</TD></TR></TBODY></TABLE><P> </P><P>Relationships:</P><P>SystemUser[Employee ID] one-to-many IntermediateTable[Owner ID] , cross filter direction is single from SystemUser -- > IntermediateTable</P><P>Account[AccountID] one-to-many IntermediateTable[Account ID], cross filter direction is BOTH</P><P> </P><P>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 :</P><TABLE border="1"><TBODY><TR><TD> </TD><TD>Account A</TD><TD>Account B</TD></TR><TR><TD>Employee 1 |</TD><TD>5 miles</TD><TD>10 miles</TD></TR><TR><TD>Employee 2 |</TD><TD>7 miles</TD><TD>12 miles</TD></TR></TBODY></TABLE><P> </P><P>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.</P>Fri, 27 May 2022 20:22:01 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Removing-filters-in-a-distance-measure-when-tables-have-a-common/m-p/2544698#M71788kstiegler2022-05-27T20:22:01ZRe: Removing filters in a distance measure when tables have a common table in between
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Removing-filters-in-a-distance-measure-when-tables-have-a-common/m-p/2544876#M71796
<P><LI-USER uid="143437"></LI-USER> , can you create a column?</P>
<P>If they are related, You can bring in a column using related or relatedtable in another table </P>
<P> </P>
<P>refer 4 ways to copy data from one table to another<BR /><A href="https://www.youtube.com/watch?v=Wu1mWxR23jU" target="_blank">https://www.youtube.com/watch?v=Wu1mWxR23jU</A><BR /><A href="https://www.youtube.com/watch?v=czNHt7UXIe8" target="_blank">https://www.youtube.com/watch?v=czNHt7UXIe8</A></P>
<P> </P>
<P> </P>Sat, 28 May 2022 00:27:53 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Removing-filters-in-a-distance-measure-when-tables-have-a-common/m-p/2544876#M71796amitchandak2022-05-28T00:27:53Z