September 20, 2020

The filter function searches in a collection for one or more conditions and removes all items that don’t fit that condition.

Usage

It follows a simple pattern.

  1. Table
  2. Formula(s)

Notice that the conditions are formulas, meaning that you can combine multiple functions in a complicated formula as long as it returns true or false. The Filter Function combines all of them into a“AND” operator. The function will discard all values that don’t validate the condition as true.

Example:

Filter( Employee, PayCheck > 10000 )

Filter( Employee, PayCheck > 10000, PayCheck < 30000 )

You can combine both formulas using an AND operator. The next result will be the same as the one before:

Filter( Employee, PayCheck > 10000 && PayCheck < 30000 )

You can use other operators like “in.”

Filter( Employee, "Portugal" in Location )

You can use elements in Power Apps like search boxes:

Filter( Employee, StartsWith( Name, SearchInput.Text ) )

You can also use OR operators to get other results:

Filter( Employee, StartsWith( FirstName, SearchInput.Text ) || StartsWith( LastName, SearchInput.Text ) )

Limitations

  1. When possible, this function will delegate to the data source the filtering. If you don’t know what this is, please check my article on delegation and my reference table of the data sources where Filter will be delegable if not only show the first 500 values (default value up to 2000).

Recommendations:

  1. There is no documented limit for the filters, but try to keep them to a minimum. If you include too many formulas, you’ll have one of two  outcomes:
    1. Your formula will be complex and, if you have strange results, you’ll have a lousy time debugging it.
    2. If the function is delegatable, the data source may take time to parse all the results, especially if there are a lot of them.
  2. Use variables to do calculations. You can use variables to do calculations before providing the variable in the Filter. Then you’ll have a re-usable condition (can be used in multiple Filter or even in other Functions) and have a clean Filter Formula.
  3. Be careful with the case if you’re filtering by strings. “Manuel” is not the same as “MANUEL,” so always be sure that the values are comparable.
  4. Always be sure that you’re comparing the correct types. For example, if you’re looking for a string inside a number, the formula may fail. But if you search a number inside a string, it may not, but you’ll not get the desired result.

Localization

  1. Please note that formulas may have localization-based differences. For example, you should write “Mod(10,3)” with “,” separating each of the arguments, but if your Portuguese regional settings you should use “;” instead.

Sources:

The filter function in PowerApps

Photo by Tyler Nix on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply