Answered

Distinct Filter Collection for Gallery

(0) Share

Report

Posted on by Phineas

5,195 Super User 2025 Season 2

I am trying to write a formula in the 'OnVisible' of a screen.

I have an SP List that contains Projects. The Projects can have one or multiple activities.

Gallery_1 should only show the Projects once.

I am using the following formula in the screen 'OnVisible' to create a Collection - (Value and ThisRecord are the only option provided by Power Apps)

   ClearCollect(colPendingUniqueDropData, ForAll(Distinct(Drop_Backup_Collection, ThisRecord),Value))

The Collection created (shown below) shows the Project (in this case Project No. 3) twice. The Project has two activities. The collection should only show the
Project once.

 
 
The Gallery_1 formula is below. Drop 3 should only show once, even though there are two related activities.
   If(PJ_Job_Type_Dropdown.Selected.Value = "Drop" && IsBlank(PJ_Location_Dropdown.Selected.Value),
      Filter(Drop_Backup_Collection,Invoice_Type = PJ_Job_Type_Dropdown.Selected.Value),

Categories:

Using Apps Using Formulas

I have the same question (0)

All responses (7)

Answers (1)

ronaldwalcott 3,827 Super User 2025 Season 2 on at

Like (0)

Report

Distinct Filter Collection for Gallery

What are you trying to achieve with this For All

ForAll(Distinct(Drop_Backup_Collection, ThisRecord),Value))

How many records are in the table and are they unique?

Wouldn't Distinct(Drop_Backup_Collection, ThisRecord) just return all of the records unless you have duplicate records?

Was this reply helpful? Yes No
Phineas 5,195 Super User 2025 Season 2 on at

Like (0)

Report

Distinct Filter Collection for Gallery

I am using a test List with three 'Projects'.

One of the projects (Project 3) has two activities, which means the project
information appears twice in the List.

I am trying to write a distinct filter for the gallery, so that ALL project data
only shows once, regardless of how many times the project shows on the list
because of the number of activities associated with the project.

To your final point, yes, there are duplicate Projects - which are actually the
same project, based on the number of activities associated with each project,
throughout the list.

Was this reply helpful? Yes No
Paolodp 2 on at

Like (0)

Report

Distinct Filter Collection for Gallery

Hi Phineas,

I think the problem is that you don't define which field to make the distinct.

In the row:

ClearCollect(colPendingUniqueDropData, ForAll(Distinct(Drop_Backup_Collection, ThisRecord),Value))

In the distinct, with "ThisRecord" it controls all data, if only one data in column is different, sees it as a different record.

You need to change "ThisRecord" with the column you want to make the Distinct like "Title"

Let me know if it work ;)

Was this reply helpful? Yes No
BD-01080338-0 44 on at

Like (0)

Report

Distinct Filter Collection for Gallery

Changing 'ThisRecord' to 'Title' (which is the Work Order No. column where "3" is located), returns only the 'Value' of "3.

Also, this changes only allows 'ThisItem.Value' (which is "3") or ThisItem.Record (which provides no content) as choices in each field of the gallery rather than ALL the columns from the List/Collection to choose from.

Was this reply helpful? Yes No
ronaldwalcott 3,827 Super User 2025 Season 2 on at

Like (0)

Report

Distinct Filter Collection for Gallery

Sorry, I don't understand the relationship between your screen and your tables.

What is Drop_Backup_Collection and does it contain the activities?

If this contains the activities why are you using it display projects and not the original projects file?

Shouldn't project and activities be a parent - children relationship? How does the screen represent that?

Why are you trying to get the distinct value when you should already have it in uniquely expressed?

Distinct will not work as suggested unless you get the unique key values (which you have not shown) and perform a lookup which I would not suggest unless you have no choice based on the design.

Was this reply helpful? Yes No
Phineas 5,195 Super User 2025 Season 2 on at

Like (0)

Report

Distinct Filter Collection for Gallery

There are multiple 'Project' types (Drop, Install, Storm Work, Trip).

There is an SP List for each Project type.

Each Project can appear as one or more line items in the SP List based
on the number of 'Activities' associated with the Project.

There can be one or multiple 'Activities' associated with each Project.

Below is a sample from the 'Drop' SP List.

Each Project is assigned a 'Work Order Number' (the Title/Invoice Number in the below).

In the example, line items 1, 2 and 4 are the same company.

The lines 1 and 2 (Project 3) Project columns contain the same data. The Project 'Activity'
columns have different data, for the same Project.

My Power Apps Gallery, which is isolated to ONLY the Project data columns (NO ACTIVITY
DATA COLUMNS), should only show Project 3, once. But as you can see in the image,
Project 3 is appearing twice.

I have tried the formula with '...Title), Value)'. That works for the distinct, showing Project 3 once,
but it only returns the Title column, no other data. Using '...ThisRecord), Value) returns all data
desired, but does not enforce the Distinct requirement, and shows Projects multiple times.

SharePoint Drop List -

Below shows the gallery of data from the above SP List, using a formula where I am using Distinct on the source Dataset (Drop_Backup_Collection)
to create a 'unique' Dataset (colPendingUniqueDropData) that I can further filter. For whatever reason, I am unable to remove the duplicate.

ClearCollect(colPendingUniqueDropData, ForAll(Distinct(Drop_Backup_Collection, ThisRecord),Value))

Power Apps Gallery -

Was this reply helpful? Yes No
Verified answer

ronaldwalcott 3,827 Super User 2025 Season 2 on at

Like (0)

Report

Distinct Filter Collection for Gallery

Why don't you have a separate projects table linked to an activities table showing the activities related to the project?

To get Distinct to work how you expect you have to use Distinct on something like the 'Invoice Number' (if this is the identifying key value) and then lookup using this value to get the other details.

Was this reply helpful? Yes No