create filter from multiple data sources

Question

I have multiple data sources that have city, state and country information.

Example -

Source 1:
ID      City        State       Country
12345   New York    New York    USA
12344   Cebu City               PHL
12232   Bengaluru   Karnataka   IND

Source 2:

ID      City    State    Country
12345   Dallas  Texas    USA
12344   London           UK

I would like to create a filter to show drill down option into country, state and city using both the databases. I cannot combine the source due to few sourcing issues. Not sure if a set can be created like union of the columns from the data sources to be able to show a filter that will have values from all the data sources.

Like..

Country = USA, PHL, IND, UK and then filter state and city and so on. 
Can someone please advise how I can achieve this? The city, state and country columns are dynamic in my data sources

Answer 1

Hi Nithin,

1. You can perform full outer join on both the sources to get the country from both of them to apply filter. Join based on Country.

2. Now you would be able to drill down using country, state and city.

hope this helps you.