How to reveal alternative information in a table based on a selected map point

[g1lgamesh]

Hi All-

I have a normal power bi map visual along with a table beside it. The map consists of a number of lat/long points, the table displaying accompanying information regarding the map points. I have a slicer for each of the map points. When a selection is chosen on the slicer, the table displays that map point along with the closest 4 other map points (have a haversine formula measure for distance). For reference, to get just those points I'm using a filter for the map points using a Top N, showing the bottom 5 by a Distance in Miles (haversine) measure.

What I'm try to do is remove the slicer altogether and simply have the user select a map point on the visual and the table would then display what the slicer normally would. Currently, the table only adopts the intel of the selected map point, not the other 4 points. I figured there would be an isfiltered type of measure that would handle it but I've been struggling to find a solution.

Thanks all.

[Anonymous · Accepted solution]

Hi @g1lgamesh ,

Please try this measure:

Measure = 
VAR __cur_city = SELECTEDVALUE('DimCity'[City])
VAR __cur_latitude = CALCULATE(MAX('Table'[Latitude]), 'Table'[City] = __cur_city)
VAR __cur_longitude = CALCULATE(MAX('Table'[Longitude]), 'Table'[City] = __cur_city)
VAR __lat1 = RADIANS(__cur_latitude)
VAR __lon1 = RADIANS(__cur_longitude)
VAR __table = 
SUMMARIZE(
    ALL('Table'),'Table'[City],'Table'[Latitude],'Table'[Longitude],
    "@distance",
    VAR __lat2 = RADIANS('Table'[Latitude])
    VAR __lon2 = RADIANS('Table'[Longitude])
    VAR __dlat = __lat2 - __lat1
    VAR __dlon = __lon2 - __lon1
    VAR __a = SIN(__dlat/2)*SIN(__dlat/2) + COS(__lat1) * COS(__lat2) * SIN(__dlon/2)*SIN(__dlon/2)
    VAR __c = 2 * ASIN(MIN(SQRT(__a), 1))
    VAR __R = 6371
    RETURN
    __R * __c
)
VAR __top5_table = SELECTCOLUMNS(TOPN( 5, __table, [@distance], ASC),[City])
VAR __result = SWITCH( TRUE(), ISBLANK(__cur_city), 1, SELECTEDVALUE('Table'[City]) IN __top5_table, 1 )
RETURN
__result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[g1lgamesh]

Thanks for the reply Gao. I'll be accepting that as a solution, as it does work, but wanted to confirm where my error was happening: My map visual was set so that the points on it were set to the "receiving" or "to" location, rather than the "from" location, which meant my table would only show one value, rather than all possible values. Hope that makes sense.

Thanks again for the measure.