Issue filtering dashboard by city/state hierarchy

[d_lueck]

Hello, this may have been asked in the past but I could not find any solutions thus far.

Here's what I am trying to do:

I have data involving 2 vendors and costs (by location) and I want to create a report that has a slicer on the side with all of the states in the USA, a map, a table, and line graph.

The fields I have to work with are:

City, State, Location (merged column combining 'City, State'), Vendor, Total_cost, Order_volume, and Unit_price

When the dashboard is opened, I'd like for it to orginally show the data by state comparing the vendors. So the map with pie charts on each state, line graph showing average cost per state, and table with a list of 'order' with cooresponding location, cost, etc. 

This I have been able to accomplish...

My issue has been trying to get all the visuals working together when I use the slicer to pick a state. I'd like the line graph to focus in on the selected state and break down into the cities in the state. Right now it stays displaying only that selected state with a single point for each of the vendors and I have to use a visual filter/location heirarchy to 'go to the next level'.

Probably the issue: I have the map using the field I created by combining City and State, whereas every other visual is using the State/city hierarchy. This is because when I try the heirarchy, the map doesn't recognize all my cities properly. The map does fine on a state level, but when I move down the hierarchy to city the map returns with several international locations.

I assume a page, report, or drilldown filter should be used but I cannot get this to function properly. Any and all advice will be greatly appreciated! 

I hope I provided enough information, please reach out if you need more context or information to help address my issue.

[v-huizhn-msft]

Hi @d_lueck,

Based on your description, you can drill down your report because you created a hierarchy. So I am not quite sure what's your meaning of drilldown filter. After research, Drill filters other visuals may be helpful. There is an option is on by default for all new reports, but you can turn it off (or turn it on for older reports), through the option in the Format tab. As you drill down in a given category in your visuals, you can have it also filter all visuals on your page by that same category. More details, please review "Drill filters other visuals" part of this article.



Best Regards,
Angelia