Sequential selections:Returning results in a table depending on the selection in another table

[Anonymous]

Hi all!

I'm working on a report which has information about cities. I'd like to make the user fill the data in an ordered way (so he would first have to choose a continent in a slicer, then choose a country in a table and, finally, get the data for the cities in that country). Unfortunately, when I choose a continent, both tables countries and cities are automatically filled up. 

These are my tables:

Continents:

continent_id	continent_name
1	Europe
2	America

Countries:

country_id	continent_name	continent_id
1	France	1
2	Spain	1
3	USA	2
4	Argentina	2

Cities:

city_id	city_name	country_id	population
1	Paris	1	2,1
2	Madrid	2	3,2
3	San Francisco	3	0,8
4	Buenos Aires	4	2,8
5	Lyon	1	0,5

I'd like to get a dashboard with a slicer for continent and two tables (for country and cities):

• The first slicer would contain the continent names. If no continent is selected, tables country and cities should remain empty
• If a continent is selected, the first table should show data to choose a country. The second table should remain empty. 

• Finally, if a country is selected, the cities information will appear in the table cities. If I select a city in the table, the slicer and the table  with country information should keep their relationships

Could you indicate me how to make this "ordered" selection? I'm struggling with this and haven't found any solution  😞

Regards

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Based on my understanding,you only need 2 measures as below:

Measure = IF(ISFILTERED('Continents'[continent_name]),"choose a country",BLANK())

Measure 2 = 
var _table=CALCULATETABLE(VALUES(Countries[country_name]),FILTER(ALL(Countries),'Countries'[continent_id]=SELECTEDVALUE(Continents[continent_id])))
Return
IF(MAX('Countries'[country_name]) in _table,MAX('Countries'[country_name]),BLANK())

Then create a relationship as below:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!