Showing results for 
Search instead for 
Did you mean: 

Filtering multiple column headers in PowerApps

Making sure that users have access to the right information at the right time is imperative to the success of any web\mobile application. At the same time, we have to avoid having too many design elements, different screens or view options as this might be confusing to the user as well as impact system performance.


This article assumes a basic understanding of PowerApps, galleries\data tables, data sources, filters and collections.


The following is an example of what can be built by following this article: 




  1. Create a gallery or data table
  2. Specify the data source and relevant columns
  3. Create collections to store the search options
  4. Create drop down boxes at the top of the columns for the user to select from.
  5. Add the filter formula for the gallery or data table.items
  6. Other considerations
  7. References


Step1.  Create a gallery or data table to your data.

As part of this article, we have created a collection for testing purposes, to trigger screen1.onstart.

{title:"Test123",department:"Operations",requester:"Dawid van Heerden",responsibleUser:"Jack"},
{title:"Test456",department:"Sales",requester:"Joe",responsibleUser:"Dawid van Heerden"},

**If you are recreating this, please keep in mind that you would first have to restart the app for the screen1.onstart to trigger and populate the collection with data.


To make sure that the collection has data, go to View\Collections to see your handy work.

View Collections.png


Collection Sample.png


Now we are ready to add the data table or gallery.

 Insert data table.png


Step2. Specify the data source and columns to display.

 Specify data source.png


Step3. Create collections to store the search options for the different search drop downs on the screen:onvisible, or refresh button.onselect.


**If you are using a SQL view, it does not support automatic datasource refreshes at the moment.

**Again please remember to restart if you are using the screen1.onvisable


screen1.onvisible or refresh button.onselect=
ClearCollect(SearchOptions_Department, {Result:"-All-"});

ClearCollect(SearchOptions_Requester, {Result:"-All-"},{Result:"-Me-"});

ClearCollect(SearchOptions_Responsible, {Result:"-All-"},{Result:"-Me-"});

The following line from the above manually adds the "-All-" and "-Me-" options to the collection of options, which could later be used in the filter formula. Options for "-Today-" or "-My Team-" are other ideas that could be added as additional options.

ClearCollect(SearchOptions_Requester, {Result:"-All-"},{Result:"-Me-"})

Step4. Create drop down boxes at the top of the columns for the user to select from. You might also want to include a normal text input for the various other fields in the data source.

Specify Drop Down Collections.png 


Step5. Add the filter formula for the gallery or data table.items

The following can be changed according to your exact requirements, but in it's most basic form, this will filter the data based on your selection. You might have to select the columns to display after you change the data source settings from a collection to custom one.



        Screen1_Search_Text.Text in title,
        Substitute(Screen1_Search_Department.Selected.Value,"-All-","") in department,
        Substitute(Substitute(Screen1_Search_Requester.Selected.Value,"-All-",""),"-Me-",User().FullName) in requester,
        Substitute(Substitute(Screen1_Search_Responsible_User.Selected.Value,"-All-",""),"-Me-",User().FullName) in responsibleUser
title, Descending)

Here we can see the data showing correctly for the selection "-Me-" under the requester column. Nice! Smiley Happy

showing the correct data.png



Other considerations:

-Filtering with blank data (two double quotes) returns all the data, which is why the substitute on "-All-" to blank works well.

-If you are getting blue exclamations while writing filter formulas, it means that delegation can't happen to the data source, and your browser or device will be busier than it should be. See below reference article on delegation.

-If formulas get ugly, paste the formula into visual studio code and change text to C# (free with reference below) to make sense of it all




Data Filter:

VS Code:


Example of VS Code: