How to Filter a Collection Based On Another Collection in Power Apps

This Power Apps tutorial will discuss how to filter a collection based on another collection in Power Apps with various examples.

A Power Apps requirement recently came up where I had to filter a collection based on other collections. And I required the result to be shown in a Power Apps Gallery Control.

How to Filter a Collection Based On Another Collection in Power Apps

So, let’s see how to filter a Collection Based On Another Collection in Power Apps using different scenarios.

Example – 1:

• Here, there are two Power Apps Button controls and two Gallery controls. Whenever a user clicks on the first button [colTechnologies], it will create a collection and display in the first gallery control.
• Similarly, when the user clicks the second button [colTSinfotechnologies], it will create another collection and display it in the second gallery control.
• Finally, I want to filter the first collection values based on the second collection values and display the result in the Dropdown control.
• The dropdown control in the image below displays all the common values from both collections.

To achieve this, follow the steps below:

• Select the first button and set its OnSelect property to the code below:

OnSelect = ClearCollect(
    colTechnologies,
    {Technology: "Power Platform"},
    {Technology: "SharePoint"},
    {Technology: "Salesforce"},
    {Technology: "Python"},
    {Technology: "SAP"}
)

Where,

1. colTechnologies = Collection Name
2. Technology = Collection Header
3. “Power Platform“, “SharePoint“, “Salesforce” and so on = Specify the records/values that you want to store in the collection

• Select the second button and set its OnSelect property to the code below:

OnSelect = ClearCollect(
    colTSinfoTechnologies,
    {TSinfoTechnology: "Database"},
    {TSinfoTechnology: "Salesforce"},
    {TSinfoTechnology: "Python"},
    {TSinfoTechnology: "Azure"},
    {TSinfoTechnology: "SharePoint"},
    {TSinfoTechnology: "Power Platform"}
)

Where,

1. colTSinfoTechnologies = Collection Name
2. TSinfoTechnology = Collection Header
3. “Database“, “Salesforce“, “Python” and so on = Specify the records/values that you want to store in the collection

• Next, select the Gallery controls and set its Items property as:

Items  = colTechnologies                       // for first gallery
Items = colTSinfoTechnologies           // for second gallery

• Insert a Dropdown control and set its Items property as:

Items = Filter(
    colTechnologies,
    Technology in colTSinfoTechnologies.TSinfoTechnology
).Technology

Refer to the below image:

This is how to filter a collection based on another collection in Power Apps.

Example – 2:

Now, let’s see the second example. Suppose you want to store the above collection result in another new collection [colResult], displayed in the gallery control below. To do so, refer to the code below.

• Select the button control and apply the code below on its OnSelect property as:

OnSelect = ClearCollect(
    colResult,
    Filter(
        colTechnologies,
        Technology in colTSinfoTechnologies.TSinfoTechnology
    ).Technology
)

Where,

1. colTechnologies = First Collection Name [from the above example]
2. Technology = First Collection Header [from the above example]
3. colTSinfoTechnologies = Second Collection Name [from the above example]
4. TSinfoTechnology = Second Collection Header [from the above example]

• Finally, select the gallery control and set its Items property to the collection:

Items = colResult

This is how to filter the Power Apps collection based on another collection.

Example – 3:

• Here, there are two Power Apps Button controls and two Gallery controls. Whenever a user clicks on the first button [colCarDetails], it will create a collection and display in the first gallery control.
• Similarly, when the user clicks the second button [colUniqueCars], it creates another collection and displays it in the second gallery control.
• Finally, I want to filter the first collection values based on the second collection values and display the result in the third collection [colResult].
• The gallery control in the image below displays all the uncommon values from both collections.

To work around this, follow the steps below:

• Select the first button and set its OnSelect property to the code below:

OnSelect = ClearCollect(
    colCarDetails,
    {Car: "Volvo"},
    {Car: "BMW"},
    {Car: "Audi"},
    {Car: "Toyota"},
    {Car: "Mercedes"}
)

Where,

1. colCarDetails = Collection Name
2. Car = Collection Header
3. “Volvo“, “BMW“, and so on = Specify the records/values that you want to store in the collection

• Select the first Gallery controls and set its Items property as:

Items = colCarDetails

• Select the second button and set its OnSelect property to the code below:

OnSelect = ClearCollect(
    colUniqueCars,
    {UniqueCar: "Audi"},
    {UniqueCar: "Toyota"}
)

Where,

1. colUniqueCars = Collection Name
2. UniqueCar = Collection Header
3. “Audi“, “Toyota” = Specify the records/values that you want to store in the collection

• Select the second Gallery controls and set its Items property as:

Items = colUniqueCars

• Now, to store the result in a new collection, insert a third button control and set its OnSelect property as:

OnSelect = ClearCollect(
    colResult,
    Filter(
        colCarDetails,
        !(Car in colUniqueCars.UniqueCar)
    )
)

Where,

1. colResult = Collection Name
2. colCarDetails = First Collection Name
3. colUniqueCars = Second Collection Name
4. UniqueCar = Second Collection Header/Column Name

• Next, select the Gallery control and set its Items property to the collection:

Items = colResult

• Finally, select the label control from the gallery and set its Text property as:

Text = ThisItem.Car

Refer to the screenshot below.

This is all about filter a collection based on another collection in Power Apps.

Additionally, you may like some more Power Apps tutorials:

• How to Create Empty Collection in Power Apps
• Power Apps Collection Distinct Filter
• How to Add Image in a Power Apps Collection
• How to Add Gallery Data to a Collection in Power Apps
• How to get Collection Column Names in Power Apps

In this Power Apps tutorial, we discussed how to filter a collection based on another collection in Power Apps with various examples.