Incorporating 'Distinct' into a filtered gallery

(1) Share

Report

Posted on by BKayMDOT

I have an Excel report set up like this:

External ID	Name	User ID	Security Role ID	Home Unit	Liaison
SmithJ	SMITH, JOHN	1234	12APC	1004	Manufacturing
SmithJ	SMITH, JOHN	1234	RZ3	1004	Manufacturing
OzuA	OZU, ANGIE	456	TR32	1004	Manufacturing
KyserJ	KYSER, JILL	238	YTRCLRK	1005	Manufacturing
KyserJ	KYSER, JILL	238	UXMGR	1005	Manufacturing
FrankB	FRANK, BEN	9274	TIMEAP	1009	Sales

On a Power App screen, I have a drop down with distinct 'Liaison' values. I would like to create a vertical galley that displays a list of all the home units for a specific liaison that is selected in the dropdown. I've got this formula:

Filter(Table1,'Home Unit'=liaisonselect.Selected.Value)

Using the small sample data as an example, I would like to know how to incorporate the distinct value so that, instead of displaying this in the gallery:

1004

1005

It displays, this:

1004

1005

Any guidance is appreciated!

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

Suggested answer

cha_cha 4,932 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hello BKayMDOT,

There are various ways to do it but this is the simplest:

Distinct(Table1,'Home Unit')

This will output a single-column Table that looks like this:

Value

1004

1005

Another way is via the GroupBy function but it's not straightforward. But it will be useful somewhat later if you keep developing in Power Apps.

Here's an intro about it:

https://www.matthewdevaney.com/powerapps-collections-cookbook/group-by-and-find-the-count/

Regards,

Charven
Suggested answer

Mark Nanneman 993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

If i'm following you correctly, it appears you've got an error in your Filter.

If you want to filter the collection based on the selected "Liaison" value, then you need to replace "Home Unit" with "Liaison"

Won't work

This is checking for any rows where 'Home Unit' equals the selected Liaison, which will be none.

Filter(Table1,'Home Unit'=liaisonselect.Selected.Value)

Will Work
This will check for any rows where 'Liaison' = 'liaisonselect.Selected.Value

Filter(Table1,Liaison = liaisonselect.Selected.Value)

Hope that helps!
BKayMDOT 72 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the info, but this doesn't quite address what I am looking to do. Here is a screen shot of the simple screen that I've started.

I have a drop down that allows a user to select a business area. When this is done, I would like to see two galleries. One will have a list of all the home units associated with that business area. The other will list all the employees in that business area, with just one listing for each distinct home unit/employee (no duplicates). Using the Filter(Table1,Liaison = liaisonselect.Selected.Value) formula nets the above. I just want to see one listing for home unit 1033 and one listing for the employee "Bur...(name blurred for privacy) - along with one listing for all the other home units/employees on the report.
Suggested answer

SaiRT14 1,992 Super User 2025 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
pls try the following:

Assume liaisonselect is your dropdown control with the Items property set to show distinct values of Liaison.

Set the Gallery’s Items Property:
Use the following formula to filter based on the selected Liaison and return only unique Home Unit values.

Distinct(
Filter(Table1, Liaison = liaisonselect.Selected.Value),
'Home Unit'
)

In the gallery, set the text property of a label (e.g., ThisItem.Result) to display the distinct Home Unit values.
BKayMDOT 72 on at

Like
a
(0)

Report
Copy link

Link copied!

I tried using the suggested formula:

(Filter(Table1, Liaison = liaisonselect.Selected.Value),'Home Unit')

This did not work and resulted in errors. The source of the error seemed to be incorrect use of commas. I played around with some adjustments and found that changing the last comma to a period resulted in a functioning formula, but it was still listing every row with the home unit (eg. showing '1004' multiple times). I just want a list of distinct values. In the small sample above, for example, when selecting 'Manufacturing' in the dropdown box I want to see two rows in the gallery.

1004

1005

Using the suggested formula, I would see:

1004

1004

1004

1005

1005

I somehow need to work in a 'Distinct' function into the filtering formula.