Filter Multi-Value SharePoint Columns in Power Apps Without Delegation Warnings

When building canvas apps on top of SharePoint, you quickly hit the infamous 2,000‑item delegation limit. Multi‑value columns – such as Person (Allow multiple selections), Choice (Allow multiple selections), and Lookup (Allow multiple selections) – are especially tricky because Power Apps cannot delegate filter operations on them. The standard Filter() function produces a delegation warning and only evaluates the first 2000 rows, leading to incomplete results in larger lists.

The workaround? Offload the filtering to a Power Automate flow. The flow runs server‑side using SharePoint's native OData query capabilities, so it can examine every row and return only the matching items. This approach requires no premium license and works with the built-in Power Apps V2 trigger.

In this article you will create a practical example that filters three different multi‑value column types: person, choice, and lookup. You will reuse the same flow pattern by simply adjusting the Filter Query and the trigger input.

Why the filter function cannot handle multi‑value columns

Power Apps delegates filter operations to the data source when the source supports it. For a SharePoint list, the Filter() function can delegate on simple text or number columns, but it cannot apply filters like:

• PersonColumn/Name eq "JohnDoe"
• ChoiceColumn eq "Value"
• LookupColumn/Id eq 5

These queries are not delegated and fall back to client‑side filtering, which is limited to the first 2000 items (configurable, but still a hard limit). A flow, however, executes the equivalent OData query directly on the server and returns only the matching records.

Scenario: Adventure Works Projects Tracker

Adventure Works manages a SharePoint list called Projects Tracker. The list contains these columns:

You have three separate screens in your canvas app, each letting the user filter based on one of these multi‑value columns. Instead of fighting delegation, you will build one flow that accepts a filter value and a column identifier, but for simplicity we'll create three almost identical flows – or a single flow with a switch. In this guide we'll highlight the three different OData filters.

Building the Power Automate flow

The flow skeleton is the same for all three column types:

1. Create an instant cloud flow with the Power Apps V2 trigger.
2. Add a Get items action against your SharePoint list.
3. Use a dynamic Filter Query that references the value passed from Power Apps.
4. Return the result as a JSON array using the Response action.

1. Person column filter (TeamMembers)

Add a required input field called PersonEmail to the trigger. In the Get items action, use this Filter Query:

TeamMembers/EMail eq 'triggerBody()['PersonEmail']'

The flow action will look like this (after the trigger):

{
"trigger": { "type": "PowerAppsV2", "inputs": { "PersonEmail": "" } },
"actions": {
  "Get_items": {
    "type": "OpenApiConnection",
    "inputs": {
      "host": { "connectionName": "shared_sharepointonline" },
      "parameters": {
        "dataset": "https://adventureworks.sharepoint.com/sites/Projects",
        "table": "Projects Tracker",
        "filterQuery": "TeamMembers/EMail eq '@{triggerOutputs()?['body/PersonEmail']}'"
      }
    },
    "runAfter": {}
  },
  "Response": {
    "type": "Response",
    "inputs": {
      "body": "@body('Get_items')?['value']"
    },
    "runAfter": { "Get_items": ["Succeeded"] }
  }
}
}

Notice that the Response action directly outputs the array from Get items. This array will contain all fields, including the person details for each team member.

2. Choice column filter (SkillsRequired)

Add a trigger input called SkillValue. Filter Query:

SkillsRequired eq 'triggerBody()['SkillValue']'

The same flow pattern, just replacing the input and filter string.

3. Lookup column filter (Department)

For a lookup column we filter by the ID of the looked‑up item. Add a trigger input called DepartmentId. Filter Query:

Department/Id eq 'triggerBody()['DepartmentId']'

Consuming the flow in Power Apps

After saving the flow, connect it from your canvas app. The flow appears as a new function with the inputs you defined.

Filtering by person

Add a People Picker (or a combo box that returns user email). Then call the flow from a button's OnSelect:

ClearCollect(
  colProjects,
  ForAll(
      Table(
          ParseJSON(
              FilterProjectsByPerson.Run(
                  "i:0#.f|membership|" & PersonPicker.Selected.UserPrincipalName
              ).response
          )
      ),
      {
          ID: Value(Value.ID),
          Title: Text(Value.Title),
          TeamMembers: ForAll(
              Table(Value.TeamMembers),
              {
                  Claims: Text(Value.Claims),
                  DisplayName: Text(Value.DisplayName),
                  Email: Text(Value.Email)
              }
          )
      }
  )
)

The result is stored in colProjects and can be shown in a gallery.

Filtering by choice

Use a regular combo box configured with the choices of the SkillsRequired column. On button click:

ClearCollect(
  colProjects,
  ForAll(
      Table(
          ParseJSON(
              FilterProjectsBySkill.Run(
                  ComboSkill.Selected.Value
              ).response
          )
      ),
      {
          ID: Value(Value.ID),
          Title: Text(Value.Title),
          SkillsRequired: ForAll(
              Table(Value.SkillsRequired),
              {
                  ID: Value(Value.ID),
                  Value: Text(Value.Value)
              }
          )
      }
  )
)

Filtering by lookup

Create a combo box that shows the Departments list. The Value property should be the ID of the selected department. Then:

ClearCollect(
  colProjects,
  ForAll(
      Table(
          ParseJSON(
              FilterProjectsByDepartment.Run(
                  ComboDepartment.Selected.Id
              ).response
          )
      ),
      {
          ID: Value(Value.ID),
          Title: Text(Value.Title),
          Department: ForAll(
              Table(Value.Department),
              {
                  ID: Value(Value.ID),
                  Value: Text(Value.Value)
              }
          )
      }
  )
)

Performance and delegation notes

• The flow performs the filter server‑side, so no delegation warning appears. The entire list is evaluated each time, but only matching rows are transferred to Power Apps. For very large lists (e.g., >50,000 items), you may want to add pagination inside the flow to avoid the default 5,000‑item limit of Get items. Use a Apply to each loop with a page size setting.
• The Response action returns the full item objects, including all multi‑value sub‑fields. Power Apps' ParseJSON function converts those JSON arrays into tables that can be iterated over with ForAll.
• Because this is a flow call, there is a small latency (typically 1–3 seconds). It is best used for filter‑on‑demand, not for real‑time searches on every keystroke.

Common mistakes and troubleshooting

1. Wrong OData property name – For person columns, Name contains the display name, EMail contains the email, and Claims contains the full claims token. Use the one that best matches your filter value.
2. Trigger input not matching the flow definition – If you rename an input after the flow is created, Power Apps will show the old parameter. Delete and reconnect the flow in Power Apps.
3. ParseJSON fails – The flow must return a valid JSON array. If the Get items action returns the { "value": [...] } wrapper, use body('Get_items')?['value'] in the Response.
4. ForAll loops slowing down parsing – If the dataset is large, parsing many nested ForAll calls can be slow. Consider limiting the number of records returned (e.g., using the flow's Top Count).

Final recommendation

Using a Power Automate flow to filter multi‑value SharePoint columns is a simple, license‑free way to bypass Power Apps delegation limits. It works for person, choice, and lookup columns with minimal adjustments. Although it adds a small round‑trip delay, it ensures your app always returns correct results, even on lists with thousands of items.

Make your flow reusable by passing a “filter type” parameter and using a switch inside the flow to build the appropriate OData filter dynamically – but that's a topic for another article.

References

• Original inspiration: Matthew Devaney, Power Apps Filter Multiple Person Column (No Delegation Warning)
• Microsoft Learn: SharePoint delegation in Power Apps
• Microsoft Learn: ParseJSON function