cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter a drop down list based on a single condition

I have a table with 2 columns (simplified version of course):

 

profit_centeryear_month
PC_A23202006
PC_B43202007

 

In my drop down list of profit centers, I would like to see only those profit centers where year_month is the largest value.

I tried this formula but it doesn't work:

LookUp(my_table,year_month=Max(year_month),profit_center)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Filter a drop down list based on a single condition

This is how I made it work for me:

1. I created a text label that contains the largest year_month value:

 Max(my_table,year_month)

2. I set my dropdown's item propery to:

   SortByColumns(Filter(my_table,my_text_label.Text=year_month),"profit_center")

 

It makes sense to skip the text label part and directly insert the Max formula in the dropdown formula:

SortByColumns(Filter(my_table,year_month=Max(my_table,year_month)),"profit_center")

However, for unknown reasons this does not return all the profit centers for which I have data.

View solution in original post

5 REPLIES 5
Highlighted
Power Apps
Power Apps

Re: Filter a drop down list based on a single condition

You can use the With function to calculate the maximum value, then use it inside the lookup, something along the lines of the expression below:

 

With(
    { maxYear: Max(my_table, year_month) },
    LookUp(my_table, year_month=maxYear, profit_center))

 

Another alternative, taking inpiration from @TJO solution, is to sort the table and then get the First element:

First(SortByColumns(my_table, "year_month", Descending)).profit_center

Hope this helps!

Highlighted
Resolver II
Resolver II

Re: Filter a drop down list based on a single condition

I'm not an expert and this may not be the most elegant solution...but maybe you can try sth. like this for the dropdown items property:

 

In a first step to sort the table according to descending date values:

SortByColumns(tableMyData, 'year_month', Descending)

 

And then use that sorted table/formula as the source argument for the distinct function:

Distinct(SortByColumns(tableMyData, 'year_month', Descending), 'profit_center')

Highlighted
Community Support
Community Support

Re: Filter a drop down list based on a single condition

Hi @Yoana_S ,

Could you describe more clearly about "see only those profit centers where year_month is the largest value"?

Could you tell me the data type of these two fields? I assume that year_month is number type.

1)If you means that: you want to get the largest year_month's profit_center, you should set the label's Text:

LookUp(my_table,year_month=Max(my_table,year_month),profit_center)

 

2)If you means that: you only want those larger year_month when profit_center is the same value, you could set the drop down's Items like this:

AddColumns(GroupBy(my_table,"profit_center","times"),year_month1,Max(times,year_month)
)

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User III
Super User III

Re: Filter a drop down list based on a single condition

@v-yutliu-msftwhiles agreeing with you, I think you left the Double quotes on year_month1

 

@Yoana_Syou can create a Collection:

ClearCollect(MyNewData,ShowColumns(AddColumns
(GroupBy(Datasource,"profit_center","times"),"year_month1",
Max(times,year_month)),"profit_center","year_month1"))
 
Then Use the collection MyNewData
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.
Highlighted
Frequent Visitor

Re: Filter a drop down list based on a single condition

This is how I made it work for me:

1. I created a text label that contains the largest year_month value:

 Max(my_table,year_month)

2. I set my dropdown's item propery to:

   SortByColumns(Filter(my_table,my_text_label.Text=year_month),"profit_center")

 

It makes sense to skip the text label part and directly insert the Max formula in the dropdown formula:

SortByColumns(Filter(my_table,year_month=Max(my_table,year_month)),"profit_center")

However, for unknown reasons this does not return all the profit centers for which I have data.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,996)