Using map to display selected location as well as closest suppliers - user configurable

[jimboslice]

Hello,

I have a report with a requirement to show selected locations and their nearest suppliers on a single map. I do not have access to Azure or ArcGIS. Ideally, the user should be able to input 3 values from slicers. First, the selected location/locations. Second, the top N closest suppliers - if the user wants to see just the top 1 or the top 50, it should be configurable. Finally, the allowable range - if the user wants to see just suppliers within 50km or 10km, it should be configurable.

My dataset is fairly simple. I have one table which is location identifier, location latitude, and location longitude. I have a second table which is supplier identifier, supplier latitude, and supplier longitude. I additionally have a table of suppliers which should be considered regardless of their distance so long as they exist in the same state as the location, but I feel confident about my ability to tie this in after the fact. The location table is on the scale of 5 figures, the supplier on the scale of 3.

What I have done so far which works to some extent - I did a full merge on the two tables, so that every location has a row for every supplier. I made a calculated column for the Haversine formula to get distance between every location and every supplier, and then a calculated column for rankX within the location to rank all supplier distances. Then I just plotted location lat/lon on one map, plotted supplier lat/lon on another map, and gave the user access to the calculated columns as slicers. 

However, the user would really like for this to exist on one map, where it shows all selected locations and all suppliers which fit within the selected parameters, using Location/Supplier status as a legend for coloration. I made another DAX table that unions the two tables together so that they can be plotted together, but I'm having trouble setting up the relationships/filters to allow it to work properly - it seems like I either filter out all locations or all suppliers.

Any help would be greatly appreciated on the matter, happy to provide more information or a fake dataset as required.

Thanks,

James

[DataNinja777 · Accepted solution]

Hi @jimboslice ,

I wondered why you don’t have access to ArcGIS, but to achieve your requirement in Power BI using a single map visualization, you need to structure your data model efficiently. Your dataset should include a Location Table with Location ID, Latitude, and Longitude, along with a Supplier Table containing Supplier ID, Latitude, and Longitude. Additionally, you should create a Distance Table that calculates the distance between each location and supplier using the Haversine formula. Since Power BI does not support direct geospatial calculations, you can compute the distance using the following DAX formula:

DistanceKM = 
VAR Lat1 = RADIANS(Locations[Latitude])
VAR Lon1 = RADIANS(Locations[Longitude])
VAR Lat2 = RADIANS(Suppliers[Latitude])
VAR Lon2 = RADIANS(Suppliers[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 * ATAN2(SQRT(A), SQRT(1 - A))
VAR EarthRadiusKM = 6371
RETURN EarthRadiusKM * C

Once the distances are calculated, suppliers should be ranked based on their proximity to each location. This can be done using the RANKX function to ensure that each supplier is ranked within its respective location.

SupplierRank = 
RANKX(
    FILTER(DistanceTable, DistanceTable[LocationID] = EARLIER(DistanceTable[LocationID])),
    DistanceTable[DistanceKM],
    ,
    ASC,
    DENSE
)

To visualize everything on a single map, a unified table is required. The UNION function in DAX can be used to merge locations and suppliers into a single dataset that can be plotted on the map.

MapTable = 
UNION(
    SELECTCOLUMNS(Locations, "ID", Locations[LocationID], "Type", "Location", "Latitude", Locations[Latitude], "Longitude", Locations[Longitude]),
    SELECTCOLUMNS(
        FILTER(DistanceTable, DistanceTable[DistanceKM] <= [Selected Range] && DistanceTable[SupplierRank] <= [Top N Suppliers]),
        "ID", DistanceTable[SupplierID], 
        "Type", "Supplier",
        "Latitude", RELATED(Suppliers[Latitude]), 
        "Longitude", RELATED(Suppliers[Longitude])
    )
)

To allow user interaction, slicers should be added for selecting specific locations, setting the number of top suppliers to display, and defining the allowable distance range. These filters should not be handled via direct relationships but rather through measures that dynamically filter the dataset based on the user’s selections. The final visualization should be implemented using a single Map visual where Latitude and Longitude from the unified table are used as the mapping coordinates, and the Type column is used as a legend to differentiate between locations and suppliers.

Best regards,

[jimboslice]

Hi @DataNinja777 , appreciate the response.

Don't you need an ArcGIS license to use ArcGIS? I may be misinformed about that, but my understanding was that we didn't have one in my org for me to use.

I ended up solving this since posting it, as it always seems to go. I had most of what you replied with already instituted. Your union table - can it reference measures/parameters that the user controls? I thought that all calculated tables & columns are created on refresh and thus couldn't interact with slicers in that way.

My solution was to set up two inactive relationships between my "wide" table (the full join one with the ranks and distance calculations) and my "tall" table (the union one being used to graph). Then made a measure where if the selected type was Location, it checked if it was within the filtered subset of the wide table, and if so, returned 1. If the selected type was Supplier, it checked if it was within the filtered subset of the wide table but on the second relationship. Then added this measure to the tooltip of each location and filtered for measure = 1.

Thanks for the help!