A form went out with cboTeam still holding a value from the Department the user had picked thirty seconds earlier. They changed Department, the Team combo box never cleared, and the record saved with a Team that did not belong to the new Department at all. Nobody caught it until someone tried to report against it later. The bug was not in the filter formula. It was in everything around the filter formula: what happens when the parent changes, and what happens while the form is still loading.

Why the obvious formula is not enough

The first version of a dependent dropdown almost always looks like this, on the child’s Items property:

Filter(Teams, Department = cboDepartment.Selected.Value)

It compiles, it shows the right rows in testing, and it ships with three problems still live:

  1. Duplicate entries. If Teams has one row per Department/Team pair (which it usually does, because that is also how the lookup data is maintained), filtering it directly returns every row, including repeats, instead of the distinct list a picker needs.
  2. A delegation ceiling. Filter against a live SharePoint list stops being delegable the moment you add Distinct on top of it, and even the base filter has a ceiling. Past 500 (or 2,000, if you raised the limit) records, the child list truncates silently. No error, no warning, just missing rows.
  3. A child that never resets. Nothing in that formula clears cboTeam.Selected when cboDepartment changes. The combo box keeps its old selection, visible and submittable, until the user manually reopens it.

Fixing all three means changing the data model, not just the formula.

The data model

One flat list with two columns, Department and Team, one row per valid pairing, beats both alternatives:

  • A SharePoint lookup column works for the parent-child relationship itself, but lookup columns are notoriously unreliable to filter and sort against in Power Fx, and they add a second round-trip to resolve.
  • Two separate lists (one for Departments, one for Teams with a Department reference) normalizes the data correctly but means every screen that needs the pairing has to join them itself.

A flat lookup list trades a little denormalization for a formula that stays simple everywhere it is used. If a Team ever needs to belong to more than one Department, add a second row. That is the entire migration.

Caching the parent

Load and cache the lookup list once, in App.OnStart, not per-screen:

ClearCollect(colDeptTeam, 'Departments & Teams');

ClearCollect(
    colDepartments,
    Sort(
        RenameColumns(Distinct(colDeptTeam, Department), Value, Department),
        Department,
        SortOrder.Ascending
    )
);

The detail that trips up most first attempts: Distinct always returns a single-column table, and that column is always named Value, regardless of what you fed it. RenameColumns(..., Value, Department) puts a usable name back on it. Skip that step and every downstream reference to .Department on this collection breaks.

cboDepartment.Items is just colDepartments. Nothing fancier is needed for the parent, because there is nothing for it to depend on.

The child

Build the child’s formula one function at a time before you write it as one line:

  1. Filter the cached pairs to the selected Department: Filter(colDeptTeam, Department = cboDepartment.Selected.Department)
  2. Reduce to distinct Teams: Distinct(..., Team)
  3. Rename the resulting Value column back to something usable: RenameColumns(..., Value, Team)
  4. Sort it: Sort(..., Team, SortOrder.Ascending)

Composed:

Sort(
    RenameColumns(
        Distinct(
            Filter(colDeptTeam, Department = cboDepartment.Selected.Department),
            Team
        ),
        Value,
        Team
    ),
    Team,
    SortOrder.Ascending
)

Set this on both Items and SearchItems if the control is a ComboBox with search enabled. SearchItems does not inherit from Items automatically. Give it its own copy of the same formula, filtered again by the search text, or the dropdown will show the right list until the user starts typing.

Delegation: why this has to run off a collection

Everything above filters colDeptTeam, a local collection, not the SharePoint list directly. That is not a style preference. Distinct is not a delegable function against SharePoint at all, and neither is a Filter layered underneath one once the pipeline gets this shape. Point the same formula at the live list instead of the cache, and past the delegation ceiling the child dropdown quietly stops offering valid Teams for Departments that happen to sort late in the list. The SharePoint delegation guide covers the general shape of this problem; cascading dropdowns are one of the places it bites hardest, because the failure looks like a data problem in the picklist, not a formula problem.

Caching once in OnStart sidesteps delegation entirely for the picker itself. It does mean the lookup list needs to be small enough to load in full, which a Department/Team pairing list almost always is. If yours is not, that is a sign that this list should not be feeding a UI picker directly.

Resetting the child when the parent changes

cboDepartment’s OnChange:

Reset(cboTeam)

That clears the control’s own selection state. It does not clear anything else that might be holding the old value: a variable, a form field, a collection. If the Team selection also gets written into varRecord as the user picks it (a common pattern so the value survives a screen change), the reset has to touch both:

Set(
    varRecord,
    Patch(varRecord, { Department: cboDepartment.Selected.Department, Team: Blank() })
);
Reset(cboTeam)

Resetting the control without clearing the stored value produces a UI that looks correct, an empty combo box, while the record underneath it still carries the stale Team. That is a worse bug than never resetting at all, because it is invisible in the app and only shows up in the data.

The load-order bug

Here is the failure mode that made the opening anecdote possible, and it has nothing to do with the reset logic above. OnChange on a control fires whenever its value changes, including when the form itself sets that value while loading an existing record. Bind cboDepartment’s DefaultSelectedItems to the saved record, and the moment the screen renders, OnChange fires, sees a “new” Department value (new from the control’s perspective, since it had nothing before), and runs the reset logic. The Team the user saved gets wiped before they have touched anything.

The fix is a context-variable guard, set in the screen’s OnVisible, before the form’s default values populate the controls:

// Screen OnVisible
UpdateContext({varInitializing: true});
// ...load the record into varRecord here...
UpdateContext({varInitializing: false});

And in cboDepartment’s OnChange, check it first:

If(
    varInitializing,
    false,
    With(
        { _newDept: Self.Selected.Department },
        Set(
            varRecord,
            Patch(varRecord, {
                Department: _newDept,
                Team: If(varRecord.Department <> _newDept, Blank(), varRecord.Team)
            })
        );
        If(varRecord.Department <> _newDept, Reset(cboTeam))
    )
)

While varInitializing is true, OnChange still fires, but does nothing. Once the guard flips back to false, OnChange starts responding to real user interaction, and the reset logic can no longer target the load itself. This is the difference between a cascading dropdown that resets correctly and one that resets at the wrong moment.

Edit mode: restoring both selections

Loading an existing record needs both combo boxes populated from saved data, with the child guarded against being blank while its parent has not resolved yet:

// cboDepartment.DefaultSelectedItems
If(
    !IsBlank(varRecord.Department),
    Filter(colDepartments, Department = varRecord.Department),
    Blank()
)

// cboTeam.DefaultSelectedItems
If(
    !IsBlank(varRecord.Team),
    Filter(colDeptTeam, Department = varRecord.Department && Team = varRecord.Team),
    Blank()
)

Both wrapped in the same IsBlank guard, both filtered against the cached collections rather than a live list, and both running while varInitializing is still true so their OnChange events do not fire the reset logic against themselves.

The classic Dropdown control and the newer ComboBox control expose selected values differently, and mixing up which one you are using is a common source of formulas that look right and return blank:

  • Dropdown.Selected.Result when the Items source is a single-column table built with Table() or similar (the implicit column is named Result).
  • ComboBox.Selected.Team (or whatever the renamed column is) when Items is a multi-column table, which is what the RenameColumns pattern above produces.
  • ComboBox.Selected.Value when the source column really is named Value, most often straight off an unrenamed Distinct.

If a formula filters against .Selected.Value and gets nothing back, the first thing to check is what the Items source actually named its column. AllowEmptySelection also matters here: without it, a ComboBox can be impossible to fully clear via Reset, which will look identical to the load-order bug above until you check the property.

Three levels, and mutual exclusion between siblings

Extending the pattern to a third level (Department → Team → Sub-team) is the same technique one layer deeper: the third dropdown filters the cached collection on both Department and Team, and its OnChange reset chains off the same varInitializing guard.

A different variant shows up when a form has several dropdowns pulling from the same pool but must not let the user pick the same value twice, three FDU (funding/expense breakdown) fields on one form, for example, where the second and third pickers need to exclude whatever the earlier ones already selected:

Filter(
    colFDUs,
    ProjectNumber = txtProject.Text,
    FDU <> cboFDU1.Selected.Value
)

The second picker’s Items excludes the first’s current selection; the third excludes both the first and second. Each box’s OnChange still needs to reset anything downstream of it, following the same pattern as the two-level case.

The named-formula alternative

For a small, fixed set of options where the “child” list is really just one of two or three hardcoded tables, a cascading dropdown is more machinery than the problem needs. A named formula or a plain If on Items does the same job with less state to manage:

// cboRegion.Items
If(
    rdoDivision.Selected.Value = "Field Operations",
    ["North", "South", "East", "West"],
    ["Admin", "Finance", "Ops Support"]
)

No collection to cache, no Distinct, no reset logic beyond what Reset already handles on its own, because there is no variable tracking a stale value across the swap. Reach for this before building the full cascading pattern if the “child” data is small and fixed rather than pulled from a growing list. The variables guide covers named formulas in more depth if this is the shape your data actually has.

Testing checklist

  • Pick a Team, then change Department. Confirm the Team combo box actually clears, not just visually but in whatever variable or form field is holding the value.
  • Open a screen bound to an existing record and confirm both selections restore correctly, and that opening the record does not silently blank the Team.
  • Push the parent list past the delegation ceiling (500 or your raised limit) and confirm the child list is still complete for every Department, not just the ones near the top.
  • Select nothing in the parent and confirm the child dropdown does not throw or show every Team unfiltered.
  • Navigate away and back. A Reset that only fires in OnChange will not re-run just because the screen reloaded; confirm the state is what you expect on return, not whatever it was left in.

Key Takeaways

  • Cache the lookup list once in App.OnStart and cascade off the collection, not the live list. This is what keeps Distinct delegable and the child list complete past the record ceiling.
  • Distinct always names its output column Value. Rename it immediately or every downstream reference silently breaks.
  • Reset() clears the control. It does not clear a variable or form field holding the same value. Clear both, or the UI will look right while the data is wrong.
  • Guard OnChange with a context variable set during OnVisible. Without it, loading a saved record fires the same reset logic meant for user interaction and wipes the value you just loaded.
  • Skip the whole pattern for small fixed option sets. A named formula does the same job with no state to manage.

The bug that opened this post was not a missing filter. It was a control that reset correctly for a user typing into it and never once for a form loading a record into it. Test both paths, not just the one that is easy to click through.