Cascading Filtering

[John-8d8e]

I have 4 tables

1. Olympics (Host Country, Host City, Sports)
2. Host Country (USA, Germany, Australia)
3. Host City (St. Louis, Atlanta, Los Angeles, Berlin, Munich, Melbourne, Sydney, Brisbane)
4. Sports (Football, Wrestling, Athletics, Swimming, Equestrian, Sailing, Handball)

I should be able to select host country (say Germany), then dropdown should filter cities (Berlin and Munich) and finally Sport events in that Olympic (all except Handball)

[Alyssa]

Hi @John-8d8e, welcome to the community! I'm happy to help you out

For this, first you would create the relationships of the items in Tables. Here's a data model I created for your example:

Here's a Host Country table:

A Host City table, with a column that is a rowlink to the Host Country table:

A Sports table, which has a rowlink to an Olympics table:

And the Olympics table, which has a filtered column for Sports:

=FILTER(Sports,"Sports[Olympics]=[Olympics]")

Now in the app, add an editable variable formatted to a rowlink of the host country table:

The city field will be a formula to only show the cities of the selected country. This is done with a FILTER formula for the source:

The sports field will then point to that filtered column of Sports we already created in the table for the given Olympics. To do this, use a FINDROW formula for the selected country and city, and point to the sports column:

=FINDROW(Olympics,"Olympics[HostCountry]=$[Country] AND Olympics[HostCity]=$[City]")[Sports]

And this is how it looks in action:

Hope this helps! Let us know if you have any further questions.