Cascading Dropdowns in Canvas Apps (Yes, It's Harder Than It Should Be)

Cascading dropdowns are one of the most common UI patterns in business apps. You pick a value in one dropdown, and the second dropdown filters based on your selection. Country to City. Department to Team. Category to Subcategory.

The concept is simple. The execution in canvas apps has a few sharp edges.

The Basic Pattern

Assume you have a Locations table in Dataverse with columns: Country, City, and Office Name.

Dropdown 1: Country

Set the Items property of the first dropdown:

Distinct(Locations, Country)

This gives you a unique list of countries from the data.

Dropdown 2: City (Filtered by Country)

Set the Items property of the second dropdown:

Filter(Locations, Country = drpCountry.Selected.Value)

drpCountry.Selected.Value references the currently selected value in the first dropdown. As the user changes the country, the city list automatically updates.

If you want to show distinct cities only:

Distinct(
    Filter(Locations, Country = drpCountry.Selected.Value),
    City
)

Dropdown 3: Office (Filtered by City)

Take it one level deeper:

Filter(
    Locations,
    Country = drpCountry.Selected.Value,
    City = drpCity.Selected.Value
)

Using Combo Box Instead of Dropdown

Combo Box controls give you search/type-ahead functionality, which is better for long lists. The pattern is the same — just set the Items property:

// Combo Box for City
Filter(Locations, Country = cmbCountry.Selected.Value)

For Combo Box, you may also need to set:

• DisplayFields: ["cr5e8_city"] (the schema name of the column to display)
• SearchField: "cr5e8_city" (the column users can type to search)

Resetting the Child Dropdown When the Parent Changes

This is the part that trips people up. When the user changes the Country dropdown, the City dropdown should reset to blank — not keep showing the previously selected city from a different country.

Method 1: Reset Function

Add this to the OnChange property of the parent dropdown:

Reset(drpCity)

If you have three levels:

// drpCountry.OnChange
Reset(drpCity);
Reset(drpOffice)

// drpCity.OnChange
Reset(drpOffice)

Method 2: Context Variable with Default

Set the child dropdown’s Default property to a context variable:

// drpCity.Default
locDefaultCity

// drpCountry.OnChange
UpdateContext({ locDefaultCity: Blank() })

This approach gives you more control but is more verbose. The Reset() method is cleaner for most cases.

Using Choice Columns Instead of a Related Table

If your cascading data comes from Dataverse Choice columns (option sets) rather than a related table, the approach is slightly different.

For a table with a Category choice column and a Subcategory choice column, you’ll need a mapping table or a configuration table that defines which subcategories belong to which categories. Choice columns don’t have built-in parent-child relationships.

The alternative: use a separate Configuration table with columns Category and Subcategory, and filter against that:

// Items for Subcategory dropdown
Filter(
    ConfigCategoryMapping,
    Category = drpCategory.Selected.Value
).Subcategory

Making It Delegation-Safe

If your source table has more than 2,000 records (the default delegation limit), Distinct() won’t return all values. It only processes the first 2,000 rows.

Option 1: Increase the Delegation Limit

In App settings, you can raise it to 2,000 (default) up to a maximum of 2,000. Actually, it goes up to 2,000 by default but can be raised to a maximum of 2,000 in most cases. Wait — the real cap is that you can set it up to 2,000 with the data row limit setting being adjustable to 2,000 max in the app settings.

Let me be precise: the data row limit in canvas app settings can be set between 1 and 2,000. The delegation limit separately defines which operations are delegable. Distinct() is not delegable against Dataverse, meaning it only processes the first N rows (where N is your data row limit, max 2,000).

Option 2: Use a Separate Lookup Table

Instead of pulling distinct values from the main table, create dedicated tables:

• Countries table with one row per country
• Cities table with a lookup to Countries

Then your dropdowns reference these small tables directly:

// Country dropdown
Countries

// City dropdown
Filter(Cities, Country.Name = drpCountry.Selected.Name)

This is fully delegable because you’re filtering a table by a column value, not using Distinct().

Option 3: Cache with ClearCollect

Load the data into a collection on App.OnStart:

ClearCollect(colLocations, Locations)

Then filter the collection instead of the data source. Collections are processed locally, so delegation doesn’t apply. This works for tables under ~10,000 rows. Beyond that, the initial load becomes too slow.

Full Working Example

// App.OnStart or App.Formulas
ClearCollect(colLocations, Locations);

// Screen: drpCountry.Items
Distinct(colLocations, Country)

// Screen: drpCity.Items
Distinct(
    Filter(colLocations, Country = drpCountry.Selected.Value),
    City
)

// Screen: drpCountry.OnChange
Reset(drpCity)

// Screen: lblSelected.Text
"Selected: " & drpCountry.Selected.Value & " - " & drpCity.Selected.Value

Common Mistakes and How to Fix Them

Child Dropdown Shows Stale Data After Parent Changes

You forgot to reset the child dropdown. Add Reset(drpCity) to the parent’s OnChange property. Without it, the child dropdown keeps its previous selection, which may not even exist in the new filtered list.

”Selected” vs “SelectedText” vs “Selected.Value”

• drpCountry.Selected — Returns the full record (for Dropdown, this is { Value: "USA" })
• drpCountry.Selected.Value — Returns the text value "USA"

If your filter isn’t matching, you might be comparing a record to a string. Always use .Selected.Value when comparing to text columns.

Empty Dropdown Shows an Error or Placeholder Text

When the parent changes and no items match the filter, the child dropdown is empty. Set a sensible PlaceholderText like “Select a city” and handle the Blank() case in your downstream logic:

If(IsBlank(drpCity.Selected.Value), "No city selected", drpCity.Selected.Value)

Delegation Warning on Filter

If you’re filtering the source table directly (not a collection) and the table has more than 2,000 rows, make sure your filter column is indexed in Dataverse and you’re using a delegable operator (=, <>, StartsWith). Using in or exactin on text columns may not be delegable.

Cascading Doesn’t Work with Dataverse Choice Columns Directly

Choice columns (option sets) don’t have built-in relationships. You can’t filter one choice column based on another. Use a mapping table or switch to lookup columns if you need cascading behavior.