Power Platform Community Forum Thread Details

Hi!
I am trying to create a combobox / dropdown to filter a gallery.

I want the Items of this filter to be the distinct values of the different dates in the table.

Example:

I have the following data:

* 10 records with a date within 2022

* 1 record with a date within 2021

* 0 records with a date within 2020

* 100 records with a date within 2019.

I want the filter to have these values:

2019

2021

2022

Is there an easy way to do this?

Categories:

Building Power Apps

Hi @mrstian89,

You can use the Distinct() and Year() functions to fetch the items for your dropdown. Create a dropdown and use the following code in the Items property:

Distinct(
 tableName,
 Year(dateField)
)

Swap tableName and dateField to the actual names in your case.

In order to filter based on the dropdown selection your Items property of your gallery would need the following code:

Filter(
 dataSourceName,
 //Filter if a year is selected
 Dropdown1.Selected.Result = Blank() || Year(dateField) = Dropdown1.Selected.Result
)

However, this is not delegable, meaning that it will only work with smaller lists (>500 or >2000 depending on settings).

Should you need a delegable alternative, you can use:

Filter(
 dataSource,
 Dropdown1.Selected.Result = Blank() || (dateField >= Date(
 Dropdown1.Selected.Result,
 1,
 1
 ) && dateField <= Date(
 Dropdown1.Selected.Result,
 12,
 31
 ))
)

If this answers your question, would you be so kind as to accept it as a solution.

Thanks!