Forum Discussion
Using MEDIAN as LOOKUP column?
Hello,
I want to LookUp records based on the record closest to the Median value of a column.
Example:
MAP_ZOOM_LAT =
VAR varMid = MEDIAN(table[column1])
RETURN
LOOKUPVALUE(
table[latitude],
varMid,
BLANK()
)
The error I receive is:
"Function LOOKUPVALUE expects a column reference as argument number 2"
How do I determine which record is closest to the Median value in a given column?
Thank you
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
2 Replies
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
