Power Apps: Apply Multiple Filters to a Gallery



Learn how to filter PowerApps Galleries using multiple filter selections without running into delegation warnings.

Introduction:

When working with Gallery components in PowerApps, often a common ask is to be able to filter the items displayed inside of that gallery based on a filter selection field - such as a Dropdown or Text Input component.

While filtering off of a single dropdown is simple enough, when you wish to filter based on multiple filter selection fields things can begin to get messy quite quickly.

One of the issues you can run into, depending on your Items formula, is delegation warnings - also hereby known as the 'Blue Squiggles of Impending Doom' ('BSID').

I'd direct you to Microsoft's very helpful Understand Delegation in a Canvas App article for more detailed guidance on the subject, but for convenience I'll highlight the following line from that article as it most succinctly sums up what delegation helps you accomplish:

"The key to building efficient apps is to minimize the amount of data that must be brought to your device."

Without getting into the weeds too much, Delegation effectively passes your query parameters to your Data Source (SharePoint, SQL, CDS) and has it do the grunt work of actually generating the results - which it then passes back to PowerApps.

Why Does This Matter for Filtering?


By default, PowerApps has a limit for the number of items it will query from a data source when using 'non-delegable' functions to the first 500 items in the Data Source.

This effectively means that when you pass a non-delegable query to a data set of more than 500 items, the query is actually only being run against the first 500 items of that data set. If the result you're looking for happens to be item number 501, 502, 503, etc it will not be returned.

You can go into the Advanced Settings in PowerApps to bump this number up to 2,000 - but ultimately, if you are going to be dealing with large data sets you want to make as many of your functions delegable as possible.


Request:

Have the ability to filter a Gallery based on one or more Dropdowns, each with their own Checkbox that determines whether to apply the selected filter criteria or not.

Also, accomplish this through a completely delegable function.


Solution:

With that lengthy introduction out of the way, let's actually get into the solution to the problem proposed.

Before that, however, I'd like to point out that this method assumes that you want to display values in the dropdowns regardless of whether they exist in the Gallery in that moment - which may or may not already have other filters applied. There is a solution to this, but that is an article for a different day.

Ingredients:

For this example, we'll need:
Note: Sample names provided for each for the sake of following along, feel free to change as required.

  • 1 Collection ('colCustomerContacts', for use in the gallery)
    • Items Sample Schema for Reference:
Note: Although this is a Local Collection, and thus not subject to Delegation Warnings, I have confirmed it works on non-local Data Sources as well.
    • 2 Dropdowns
      • 'fldCompanyNameFilter'
      • 'fldJobTitleFilter'
    • 2 Checkboxes
      • 'chkApplyCompanyNameFilter'
      • 'chkApplyJobTitleFilter'
    • 1 Gallery
      • 'galCustomerContacts'
    Instructions:

    If you're following along using the sample schema provided, on your two Dropdowns you'll want to set the Items and OnSelect properties as follows:
    • fldCompanyNameFilter:
      • Items: Distinct(colCustomerContacts, CompanyName)
      • OnSelect: Reset(chkApplyCompanyNameFilter); Set(filterCompanyName, Blank() );
    • fldJobTitleFilter:
      • Items: Distinct(colCustomerContacts, JobTitle)
      • OnSelect: Reset(chkApplyJobTitleFilter); Set(filterJobTitle, Blank() );
    They should end up looking something like this:


    For the two Checkboxes, we'll want to modify both the OnCheck and OnUncheck properties as follows:
    • chkApplyCompanyNameFilter:
      • OnCheck: Set(filterCompanyName, fldCompanyNameFilter.Selected.Result);
      • OnUncheck: Set(filterCompanyName, Blank() );
    • chkApplyJobTitleFilter:
      • OnCheck: Set(filterJobTitle, fldJobTitleFilter.Selected.Result);
      • OnUncheck: Set(filterJobTitle, Blank() );
    Note: The bolded section of the OnCheck formulas ('Result') will vary for your own applications depending on the Items value of the given dropdown. Update this accordingly.

    Ultimately, they should look something like this:



    Finally, we'll set the Items property of our Gallery to the following:
    • galCustomerContacts:
      • Items: Filter(colCustomerContacts, IsBlank(filterCompanyName) || filterCompanyName = CompanyName, IsBlank(filterJobTitle) || filterJobTitle = JobTitle)
    Which should look like this:



    Putting It All Together:

    I'll try to explain how all of these pieces ultimately come together to fulfill our requirements as simply as possible. Before doing that, however, let me show you how I decided to lay out this app:


    The Dropdowns provide the Filter selections for us to use, which in this case are the distinct values from the given columns of our data source.

    The Checkboxes provide a mechanism for controlling whether the filters are applied or not - and also what values to filter by.

    By setting the value of the Variables ('filterCompanyName' & 'filterJobTitle') to the selected item, we are subsequently able to use them in our Gallery's Filter() function. Also, when OnUncheck is triggered we are able to clear those variables.

    Finally, the Items property of the Gallery uses the Filter() function to determine what items to show. 

    If you look closely, you'll notice that we are giving the Filter function only two Logical Tests to perform that will give us the desired results - even though we're testing four separate things (2 for each logical test). 

    Also important to note is that the Functions were using for those logical tests (IfBlank() and ='s) are delegable functions!

    What We're Effectively Saying (to the Data Source) Is:

    Logical Test 1: (IsBlank(filterCompanyName) || filterCompanyName = CompanyName)

    If filterCompanyName is blank (then I don't care to use it as a filter) - otherwise, get me items where the CompanyName matches filterCompanyName.

    Logical Test 2: (IsBlank(filterJobTitle) || filterJobTitle = JobTitle)

    And while you're at it, If filterJobTitle is blank (then I don't care to use it as a filter) - otherwise, get me the items where the JobTitle matches filterJobTitle.

    One last thing to callout that I haven't already, if you go back up to the Dropdown setup you'll notice that we did a couple of things in the OnSelect property of each. Namely, we Reset() their respective checkboxes and Set() the associated variable to Blank().

    The reason we do this is simply because if you've already checked the box (and thus set the filter value) and then subsequently change the dropdown's selection without unchecking/rechecking the box - you'll be left with an unchanged filter variable (which relies on the OnCheck property being triggered). By using this method, we are gently nudging the user to recheck (the now unchecked) box whenever they click on the Dropdown to keep everything working as expected. There may be other ways to accomplish this, but for now this works.

    Wrap-Up:

    For those of you who hung in there to read this entire post - thank you! At the end of the day, actually implementing this functionality isn't nearly as complicated as trying to put it to words (and pictures).

    Feel free to comment below with any questions or issues you may uncover when implementing this.