Simplify a Filter Function

[RiccoHipp1903]

Hello Guys,

I have a Gallery (Software-Store) which is filterd by 2 DropDown Menus and a search Text

DropDown Department : SIngle Choice Column (Values filled with a Collection [Empty Value added])

DropDown Category: Multiple Choice Column ( Values filled with a Collection [Empty Value added])

Now I filter my Gallery with the following code 

If(
    IsBlank(Dropdown_Department_Software.Selected.Value),
    /* If DropDown for Department is NULL */
    If(
        IsBlank(Dropdown_Category_Software.Selected.Value),
        /*If DropDown Category is NULL only search for text*/
        Filter(
            'Software-Liste',
            StartsWith(
                Title,
                TextInput_Search_Software.Text
            )
        ),
        /*If DropDown Categorie is Selected, Search for Text and Category*/
        With(
            {
                data: Filter(
                    'Software-Liste',
                    StartsWith(
                        Title,
                        TextInput_Search_Software.Text
                    )
                )
            },
            Filter(
                data,
                Dropdown_Category_Software.Selected.Value in Kategorie.Value
            )
        )
    ),
    /* Check for Category Dropdown */
    If(
        IsBlank(Dropdown_Category_Software.Selected.Value),
    /*If DropDown Category is NULL*/
        Filter(
            'Software-Liste',
            StartsWith(
                Title,
                TextInput_Search_Software.Text
            ) && Abteilung.Value = Dropdown_Department_Software.Selected.Value
        ),
    /*If DropDown Category also have a Value Selected*/
        With(
            {
                data: Filter(
                    'Software-Liste',
                    StartsWith(
                        Title,
                        TextInput_Search_Software.Text
                    )
                )
            },
            Filter(
                data,
                Dropdown_Category_Software.Selected.Value in Kategorie.Value && Abteilung.Value = Dropdown_Department_Software.Selected.Value
            )
        )
    )
)

Why the with?
I know there only like 4 Options in the Category Choices and overall just 50-70 Software. We all know, Filtering with Multiple Choice runs into a delegtion Warning because in isn't delegatable (in this use)
for this, I used with to preprepare Data and Filter the Category afterwards. Not 

beautiful but it Works (If it's stupid but it works, it ain't stupid 😉)

My question is, is there a more simple way to achieve my needs without that IF Construct?

[WarrenBelz · Accepted solution]

Hi @RiccoHipp1903 ,

Very long way around, but as you said, it works, however try this

With(
   {
      data: 
      Filter(
         'Software-Liste',
         StartsWith(
            Title,
            TextInput_Search_Software.Text
         ) &&
         (
            Len(Dropdown_Department_Software.Selected.Value) = 0 ||
            Abteilung.Value = Dropdown_Department_Software.Selected.Value
         )
      )
   },	  
   Filter(
      data,
      Len(Dropdown_Category_Software.Selected.Value) = 0 ||
      Dropdown_Category_Software.Selected.Value in Kategorie.Value
   )
)

The With() statement is to put some Delegation management in there as the in Filter is not Delegable, so as long as the top filter returns smaller numbers than your Delegation limit, the whole filter will work.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[RiccoHipp1903]

Hello @WarrenBelz ,

works really well. Thank you. Much easier to understand for other than my IF spaghetti. As the List will really never be higher than the Delegation limit, this is no problem to go the way around. 

[WarrenBelz]

Hi @RiccoHipp1903 ,

There is only a Delegation limitation if the result of the top formula (which is Delegable) is more than your limit - you could have 50k records in your list and it will still work on this basis. I have a blog on this that may be useful to you.