Optimizing Power Apps: Advanced Filtering Techniques for Galleries with Multi-Select Combo Boxes and Solutions to Delegation Warnings

I was asked about how to filter galleries in a multiple selectable combo box, so I will note how to do that.

スポンサーリンク

Preparation

Using the following SharePoint list,
And the following Dataverse table,
Let’s consider the formula for filtering the above two using a multi-select combo box.
※When nothing is selected, all items are displayed.
To check if delegation is effective, set the data row limit to 3.
If delegation is working→Items ‘ddd’ and ‘eee’ will still be displayed after filtering.
For more about delegation and data row limits, visit here:
Visual Guide to Power Apps: Understanding the Relationship Between Delegation and Data Row Limits (500 to 2000 Items)
It has been difficult to understand the two together, the delegation and the data row limit (the default is 500), but it...
ippu-biz.com
2023.12.21

Case for SharePoint List

The simplest formula would look like the image below, but unfortunately, when using a SharePoint list as the data source, it falls under the limitations of delegation.
To filter with multiple values while respecting delegation, it seems we must resort to a formula like the one in the following image. It’s important to note, as a caution, that filtering is possible only for the first N selections.
※In the example below, filtering is possible for the first three selections.
Filter(SampleList As item,
    IsBlank(ComboBox1.SelectedItems) || IsEmpty(ComboBox1.SelectedItems) ||
    (CountRows(ComboBox1.SelectedItems) > 0 && item.Title = Index(ComboBox1.SelectedItems,1).Value) ||
    (CountRows(ComboBox1.SelectedItems) > 1 && item.Title = Index(ComboBox1.SelectedItems,2).Value) ||
    (CountRows(ComboBox1.SelectedItems) > 2 && item.Title = Index(ComboBox1.SelectedItems,3).Value)
)
※Note that the formula below does not work for the gallery.
This is probably because when SelectedItems is zero or similar, accessing the item with the Index function causes an internal error.

Case for Dataverse

In the case of Dataverse, the simple formula shown in the image below works perfectly with the delegation function.
Filter(SampleTable, 
    IsBlank(ComboBox1_1.SelectedItems) || IsEmpty(ComboBox1_1.SelectedItems) || 
    Name in ComboBox1_1.SelectedItems.タイトル
)

Dataverse is indeed very convenient.

Bonus: When ComboBox Choices Reference a Master Table

When defining choices as a master, like in the image below,
The formula for the SharePoint list gallery becomes as follows,
And the formula for Dataverse looks like this.

Related Articles

Visual Guide to Power Apps: Understanding the Relationship Between Delegation and Data Row Limits (500 to 2000 Items)
It has been difficult to understand the two together, the delegation and the data row limit (the default is 500), but it...
ippu-biz.com
2023.12.21
How to write a clean Filter function in Power Apps｜Discussion of problems with the way it has been written in the past.
I have learnt how to write neatly when applying Filter to a gallery, and I have made notes on how I have tried and teste...
ippu-biz.com
2024.09.08

コメント

Copied title and URL