Forum Discussion
Azure Map Point ID, Order help
Hi unknown917,
The idea is that you want to rank the entries by distance, and then keep only the ones where rank = 1.
Something along the lines of this:
RankInBin = VAR sp = 'Table'[StartingPoint] VAR bin = 'Table'[DirBin] RETURN RANKX( FILTER( ALL('Table'), 'Table'[StartingPoint] = sp && 'Table'[DirBin] = bin ), 'Table'[DistanceKm], , DESC, DENSE )Next create a slim table of “farthest points” by filtering to RankInBin = 1:
OutlierPoints = FILTER( 'Table', 'Table'[RankInBin] = 1 )If you sometimes get multiple rows with the exact same max distance, add a secondary tiebreaker to the rank. One simple way is build a SortKey column and rank by that:
SortKey = 'Table'[DistanceKm] * 1000000 + (360 - 'Table'[BearingDeg]) / 1000
Then change the RANKX expression to use [SortKey] instead of DistanceKm, still DESC.If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
tayloramy - I'm struggling with 2. how to create the table with the farthest points by direction, by starting point. I created a Variant table to get the data I need, but am not grasping how to use RANKX to produce a short list of lat/long by direction, by farthest point. Any guidance would be greatly appreciated.
Hi unknown917,
The idea is that you want to rank the entries by distance, and then keep only the ones where rank = 1.
Something along the lines of this:
RankInBin =
VAR sp = 'Table'[StartingPoint]
VAR bin = 'Table'[DirBin]
RETURN
RANKX(
FILTER(
ALL('Table'),
'Table'[StartingPoint] = sp
&& 'Table'[DirBin] = bin
),
'Table'[DistanceKm],
,
DESC,
DENSE
)
Next create a slim table of “farthest points” by filtering to RankInBin = 1:
OutlierPoints =
FILTER(
'Table',
'Table'[RankInBin] = 1
)If you sometimes get multiple rows with the exact same max distance, add a secondary tiebreaker to the rank. One simple way is build a SortKey column and rank by that:
SortKey = 'Table'[DistanceKm] * 1000000 + (360 - 'Table'[BearingDeg]) / 1000
Then change the RANKX expression to use [SortKey] instead of DistanceKm, still DESC.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.