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 tested filtering and the problems I have had with it.

Advance preparation
Trial and error 1: large number of if functions
Trial and error 2: Assignments to variables
Best practice: use the Filter function’s method of determining logical expressions wisely.
Related articles.

Advance preparation

When there is a SharePoint list like the one in the image,

Consider filtering against this list in the Power Apps gallery.
If the combo box for an element is empty, it is not filtered for that element.

*To simplify the example, all combo boxes should be singular selections only (no multiple selections).

Trial and error 1: large number of if functions

When I was just learning Power Apps, I used a lot of if statements like this to create filters.
*It’s hard to write, so this time I gave up with two If functions.

Of course, the filters work as desired,

but the problem is low readability and poor maintenance.

It is hard to create if statements because they keep increasing, and it is hard to modify the code because similar expressions need to be written in various places.

Trial and error 2: Assignments to variables

The next method considered was to clean up the expression by assigning the result of the Filter expression to a variable.

Create an invisible button on the screen and use its OnSelect to filter sequentially like this.

You can set that OnSelect process to OnVisible on the screen,

Called by OnChange of each control,

And then set the created variable to Items in the gallery,

The filter works.

It is also highly readable, easy to modify the code and was once thought to be a good method.

However, once again, there are problems with this method, large and small, but the main problem is that it is caught by the ‘data row limit’.

Once assigned to a variable, the gallery read function cannot be used, and when targeting a data source with a large number of records, only 500 data can be displayed (by default).

*For information on data line limits, click here.

Power Apps 委任とデータ行の制限（500件～2000件）の関係を図にまとめてみた

これまで委任とデータ行の制限（既定は500件）が絡まると、いまいち理解がこんがらがってしまっていたけど、改めて図にしてみるとわかりやすかったのでメモ。前提前提として今回はこんなリストがあって、このリストを、わかりやすいようにデータ行の

Best practice: use the Filter function’s method of determining logical expressions wisely.

Based on these, the method learned this time is how to make good use of the Filter function’s logical expressions.

In fact, the logical expressions after the second argument of the Filter function do not necessarily refer to the value of the data source.
The specification of the Filter function is that if the logical expression is evaluated for a row (record) and the result is ‘true’, the row is retrieved.

In extreme cases, if you write a Filter logical expression as in the image, you get all the rows,

If you write it as in the image here, you will not get any rows.

*Of course, if you write a logical expression like this, look at the data source values properly! and you will get a warning.

So, if we use the specification of the Filter function wisely to create this filter this time, the expression will be as shown in the image.

For example, if no user is selected in UserCombobox_2 (if IsBlank(UserCombobBox_2.Selected) is true), the expression to the right of or (|| UserComboBox_2.Selected.Mail = User.Email) not evaluated and all rows will evaluate to true, so all rows will break through this logical expression.

Using this method, it is possible to write an expression that says that if the value of the control is empty, it will not be filtered, using only the Filter function, which is a much cleaner expression.

Furthermore, since the data source is specified directly in the gallery, there is no need to worry about ‘data row limits’.
*Of course, write it in such a way that you don’t get any delegation warnings.

The results are also filtered properly in this way, so this is the cleanest way to filter at the moment.