Filters within the report across multiple tables

[talhaparvaiz]

Hi,

REQUIREMENT:

I have a Power BI report which is based on (let's say) 3 colum table. Table looks something like this

Country     City           Application

Canada      Toronto    App1

Canada      Toronto    App2

Canada      Calgary     App3

USA           Toronto    App4

USA           Chicago    App2

USA           Detroit      App3

What I want is a dynamic report in which if someone selects a country then the options for city and applications should be filtered for that country. AND after selecting country, if someone selects city, then the report should filter for the selected Country/City.

WHAT I HAVE DONE SO FAR

I have added 3 tables in the report (using the visualization tool). In one table, I have identified only Countries and in the next Cities and the third table has applications.

When clicks on country, the city and applications get filtered and when selects the city then the filter selects application for that country.

WHAT's NOT WORKING

As you can see in the table above, Toronto is repeated for both USA and Canada. 

• When clicks on Canada then the following records shows up (as per expected)

Country     City           Application

Canada      Toronto    App1

Canada      Toronto    App2

Canada      Calgary     App3

• When clicks on Toronto, then following records show up, including the one for USA (Since I have selected Canada and then Toronto, I just want to see the ones related to Canada/Toronto filter)

Country     City           Application

Canada      Toronto    App1

Canada      Toronto    App2

USA           Toronto    App4

Please advise how to fix this issue

[v-yiruan-msft]

Hi @talhaparvaiz ,

Please try to create two slicers using the field Country and City separately as below screenshot and check whether it can get what you want. If no, could you please provide the related information for the following questions? Thank you.

1. Are the field Country, City and Application from the same table? 

2. If the above three fields comes from different tables, is there any relationship created among them?

3. How did you set your visuals? Visual type? Fields setting?

Best Regards

[parry2k]

@talhaparvaiz did you created the relationship of these tables with your main table? 

[talhaparvaiz]

can you please guide me how to do that

Thanks