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.
Power Apps Filter Collection By Another Collection

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
Filter Power Apps Collection By Another 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
how to filter a collection based on another collection in power apps
  • 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:

See also  Power Apps Azure AD Group
filter a collection based on another collection in power apps

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.

filter Power Apps collection based on another collection
  • 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]
power apps filter a collection based on another collection
  • Finally, select the gallery control and set its Items property to the collection:
Items = colResult
power apps filter collection based on another collection

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.
PowerApps Filter Collection By Another Collection

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
Filter Collection By Another Collection in Power Apps
  • Select the first Gallery controls and set its Items property as:
Items = colCarDetails
Filter Collection By Another Collection in PowerApps
  • 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
How to Filter  Power Apps Collection By Another Collection
  • Select the second Gallery controls and set its Items property as:
Items = colUniqueCars
How to Filter PowerApps Collection By Another Collection
  • 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
Filter PowerApps Collection By Another Collection
  • Next, select the Gallery control and set its Items property to the collection:
Items = colResult
Filter a PowerApps Collection By Another Collection
  • Finally, select the label control from the gallery and set its Text property as:
Text = ThisItem.Car

Refer to the screenshot below.

Power Apps Filter Collection By other Collection

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

See also  Microsoft Power Apps Copilot [With Examples]

Additionally, you may like some more Power Apps tutorials:

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

>