Forum Discussion
Using MEDIAN as LOOKUP column?
- 5 years ago
ericOnline Maybe this:
MAP_ZOOM_LAT = VAR __varMid = MEDIAN(table[column1]) VAR __Table = ADDCOLUMNS( 'table', "diff",ABS([latitude]-__varMid) ) VAR __Min = MINX(__Table,[diff]) VAR __Result = MINX(FILTER(__Table,[diff]=__Min),[ID]) RETURN [ID]That's the general idea, basically a Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Not 100% of what you are trying to return/show so I just made up an "ID" column. You could use a variation of this as a flag variable that you could use as a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
MAP_ZOOM_LAT = VAR __CurrentLat = MAX(table[latitude]) VAR __varMid = MEDIAN(table[column1]) VAR __Table = ADDCOLUMNS( 'table', "diff",ABS([latitude]-__varMid) ) VAR __Min = MINX(__Table,[diff]) VAR __Result = IF(__CurrentLat = __Min, 1, 0) RETURN __Result
ericOnline Maybe this:
MAP_ZOOM_LAT =
VAR __varMid = MEDIAN(table[column1])
VAR __Table =
ADDCOLUMNS(
'table',
"diff",ABS([latitude]-__varMid)
)
VAR __Min = MINX(__Table,[diff])
VAR __Result = MINX(FILTER(__Table,[diff]=__Min),[ID])
RETURN
[ID]
That's the general idea, basically a Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Not 100% of what you are trying to return/show so I just made up an "ID" column. You could use a variation of this as a flag variable that you could use as a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
MAP_ZOOM_LAT =
VAR __CurrentLat = MAX(table[latitude])
VAR __varMid = MEDIAN(table[column1])
VAR __Table =
ADDCOLUMNS(
'table',
"diff",ABS([latitude]-__varMid)
)
VAR __Min = MINX(__Table,[diff])
VAR __Result = IF(__CurrentLat = __Min, 1, 0)
RETURN
__Result