Forum Discussion
Filter Locations by Related Location
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!
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.
3 Replies
- Greg_Deckler
Community Champion
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
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?
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.
