Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
Hi @ericOnline ,
Has the problem be solved?
Best Regards,
Jay
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |