cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PowerAddict
Level 10

Filtering using multiple filters (text boxes and dropdowns)

Data source is CDS. I have a gallery that needs to be filtered by several filters, lets assume 5 for now. 

 

Any one of them could be blank. If one is blank, then the gallery shouldn't be filtered on that particular filter. 

 

I initially had something like this: Filter(Entity1, If(IsBlank(Dropdown1.Selected.Value), Column1 = Dropdown1.Selected.Value, true),............If(IsBlank(TextInput1.Text), TextInput1.Text in Column5, true))

 

This worked fine until we hit the delegation limit. Now, I am trying to rewrite this expression factoring in delegation. One of the things I noticed is that if I check for blank values outside of the Filter, then there are no delegation warnings. In other words, the actual filters are all delegable. 

 

However, that is almost impossible or very difficult to do because I will have to account for all the situations in which none, or one, or two, or three, or four, or all five filters could have values. 

 

I am at a loss now. Any help will be highly appreciated. Please let me know if you need any other info. 

 

Thanks,

Hardit Bhatia

https://thepoweraddict.com

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filtering using multiple filters (text boxes and dropdowns)

@PowerAddict 

To summarize your post below I believe the question is "how can I create a gallery filtered by 5 different controls while ignoring any blank controls."

 

This is what I would try to use in your situation.  The code IsBlank(TextInput1.Text)=True tells the FILTER to ignore that line when the dropdown control is Blank.

Filter(
    Entity1,
    IsBlank(Dropdown1.Selected.Value)=True Or Column1 = Dropdown1.Selected.Value,
    ...,
    IsBlank(TextInput1.Text)=True Or TextInput1.Text in Column5
)

 

I originally came up with this pattern to help another user do the same thing in SharePoint while avoiding delegation limits.

Filter(
    your_datasource_name,
    IsBlank(tbYear.Text)=true Or Year = tbYear.Text,
    IsEmpty(cbxRegion.Selected)=true Or Region = cbxRegion.Selected,
    IsBlank(tbCustomer.Text)=true Or Customer tbCustomer.Text,
    IsBlank(tbKeyword.Text)=true Or Keyword = tbKeyword.Text
)

 

Link To My Original Post:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Filtering-on-multiple-fields/m-p/40...

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Filtering using multiple filters (text boxes and dropdowns)

@PowerAddict 

To summarize your post below I believe the question is "how can I create a gallery filtered by 5 different controls while ignoring any blank controls."

 

This is what I would try to use in your situation.  The code IsBlank(TextInput1.Text)=True tells the FILTER to ignore that line when the dropdown control is Blank.

Filter(
    Entity1,
    IsBlank(Dropdown1.Selected.Value)=True Or Column1 = Dropdown1.Selected.Value,
    ...,
    IsBlank(TextInput1.Text)=True Or TextInput1.Text in Column5
)

 

I originally came up with this pattern to help another user do the same thing in SharePoint while avoiding delegation limits.

Filter(
    your_datasource_name,
    IsBlank(tbYear.Text)=true Or Year = tbYear.Text,
    IsEmpty(cbxRegion.Selected)=true Or Region = cbxRegion.Selected,
    IsBlank(tbCustomer.Text)=true Or Customer tbCustomer.Text,
    IsBlank(tbKeyword.Text)=true Or Keyword = tbKeyword.Text
)

 

Link To My Original Post:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Filtering-on-multiple-fields/m-p/40...

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

PowerAddict
Level 10

Re: Filtering using multiple filters (text boxes and dropdowns)

Thanks @mdevaney Reading the original post and how it progressed was real fun! I think this will work, I will wrap it up and reply back if it doesn't.

Thanks,
Hardit Bhatia
https://thepoweraddict.com
Super User
Super User

Re: Filtering using multiple filters (text boxes and dropdowns)

@PowerAddict 

That original post was... challenging. I was starting to think there was no possible way to do it, was almost about to give up, and then *EUREKA* together we finally found the solution.  I am crossing my fingers here and hoping it works for you too.

 

On another note: I would really like to get my hands on CDS soon. I've just started a Community Plan with the intention to give it a Test Drive.  I am sure I will be coming back to you with questions about that soon...  You are lucky to be working with such new technology!

PowerAddict
Level 10

Re: Filtering using multiple filters (text boxes and dropdowns)

Yes, it worked perfectly fine! I wanted to share it with as many people as possible so I wrote a quick blog on it and credited you and the others who were involved in the original post!
And yes, CDS is fun. It is sometimes difficult to imagine any other data source now 🙂 I am always available to answer questions, have discussions and more importantly, have fun!

Feel free to reach out any time!

Thanks,
Hardit Bhatia
https://thepoweraddict.com

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (4,123)