cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
longlostlives
New Member

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 IDLocationLocation ID
1Washington1
1Dallas2
2Washington1
3Dallas2
3St. Paul3
3Boise4
4Boise4
4Seattle5

 

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 IDLocationLocation ID
1Dallas2

 

If Dallas is selected then

 

Session IDLocationLocation ID
1Washington1
3St. Paul3
3Boise4

 

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!

1 ACCEPTED SOLUTION
longlostlives
New Member

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.

View solution in original post

3 REPLIES 3
longlostlives
New Member

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
New Member

@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
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors