Filter Locations by Related Location

[longlostlives]

I have a table that stores locations that have been selected in a particular session. Here is some example data:

Session ID	Location	Location ID
1	Washington	1
1	Dallas	2
2	Washington	1
3	Dallas	2
3	St. Paul	3
3	Boise	4
4	Boise	4
4	Seattle	5

I want to display the list of locations in a slicer, and then based on the selected location, display all the other locations that have been chosen when that location was selected.

Examples:

If Washington is selected, then

Session ID	Location	Location ID
1	Dallas	2

If Dallas is selected then

Session ID	Location	Location ID
1	Washington	1
3	St. Paul	3
3	Boise	4

I will then display the locations on a map with the count of session ID used as the size indicator.

What's the best way to go about doing this? I have a related table for all Location and Location IDs, and could use that for the slicer although I would ideally just use the list of locations from the main table so only locations that have been selected appear in the slicer list.

Thanks in advance!

[longlostlives · Accepted solution]

I ended up setting up a table in Power Query to contain all the required data instead of going the DAX route. It does the job and will monitor performance vs. trying again in DAX.

[longlostlives]

@Greg_Deckler Thanks for the tip! I am able to get the selector working to display the results in a table, but I'm having trouble making it work when I try to count the Session ID for the map size parameter.

Here's what I have for the 'basic' selector:

Related Locations = 
VAR LocationID = MAX([LocationID])
VAR Location = MAX([Location])
VAR LocationSelected = VALUES(Locations[Location])
VAR TableSelected = SELECTCOLUMNS(FILTER(ALL(LocationCities), [Location] IN LocationSelected), "LocationID", [LocationID])
RETURN
IF (LocationID IN TableSelected && Location <> LocationSelected, 1, BLANK())

Any thoughts on how to modify this or set up another measure to use this to determine how many times each related location shows up?

[Greg_Deckler]

@longlostlives I think you will need a disconnected table for your slicer and then a Complex Selector for your table.

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534