Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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_idcontinent_name
1Europe
2America

 

Countries:

country_idcontinent_namecontinent_id
1France1
2Spain1
3USA2
4Argentina2

 

Cities:

city_idcity_namecountry_idpopulation
1Paris12,1
2Madrid23,2
3San Francisco30,8
4Buenos Aires42,8
5Lyon10,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

Need Help
Tips and Tricks
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

     

1 Reply

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!