cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Ignore Filter when IsBlank

I have a table with multiple columns that supposed to be filter by a user with multiple dropdowns.

image.png

The dropdown have as first item a blank to disable the filter function. The table items have the following formula:

Filter(
    List;
    !IsBlank(FilterProjektLeiter.SelectedText.Result) && Projektleiter.DisplayName = FilterProjektLeiter.SelectedText.Result
)

 

I only implement one dropdown, the other two are missing.

The table get filtered like I expected, but when I set the dropdown to blank the table is complete empty. How can I achieve a table with a multiple on/off filter? The filter cases must cover 1 dropdown / 2 dropdown / 3 dropdown and 0 dropdown filter.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Ignore Filter when IsBlank

Try contextually applying the Filter logic. Note, this is a non-delegated function.

Filter(
    Accounts,
    If(
        !IsBlank(DropdownAccountOwnership.Selected),
        Ownership = DropdownAccountOwnership.Selected.Value,
        true
    ),
    If(
        !IsBlank(DropdownAccountParent.Selected),
        'Parent Account'.Account = DropdownAccountParent.Selected.Account,
        true
    )
)

 

My example above is using the native CDS Account entity, and fields. I have two dropdowns - one for the Ownership option set, another for a list of Parent Accounts. As you can see, the Filter logic is dynamically switching whether the dropdown is blank or not.

NOTE (again): This is not delegable, but will work for your scenario (if non-delegation is acceptable).

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: Ignore Filter when IsBlank

Hi @Nusserdt 

IsEmpty() is the proper function to use with Filter since a table is the result of the Filter() function.  IsBlank() is used for a single record. 

Highlighted
Super User
Super User

Re: Ignore Filter when IsBlank

Try contextually applying the Filter logic. Note, this is a non-delegated function.

Filter(
    Accounts,
    If(
        !IsBlank(DropdownAccountOwnership.Selected),
        Ownership = DropdownAccountOwnership.Selected.Value,
        true
    ),
    If(
        !IsBlank(DropdownAccountParent.Selected),
        'Parent Account'.Account = DropdownAccountParent.Selected.Account,
        true
    )
)

 

My example above is using the native CDS Account entity, and fields. I have two dropdowns - one for the Ownership option set, another for a list of Parent Accounts. As you can see, the Filter logic is dynamically switching whether the dropdown is blank or not.

NOTE (again): This is not delegable, but will work for your scenario (if non-delegation is acceptable).

View solution in original post

Highlighted
Super User
Super User

Re: Ignore Filter when IsBlank

Hi @Nusserdt 

 

In order for the query to be delegable and work for each dropdown, you'll need do multiple OR || conditions connected to AND && conditions as below.

 

Filter(
    List;
    (
      FilterProjektLeiter.SelectedText.Result = ""
      ||
      Projektleiter.DisplayName = FilterProjektLeiter.SelectedText.Result
    )
    &&
    (
      FilterProjektSECONDDROPDOWN.SelectedText.Result = ""
      ||
      Projektleiter.SECONDPROPERTY = FilterProjektSECONDDROPDOWN.SelectedText.Result
    )
    &&
    (
      FilterProjektTHIRDDROPDOWN.SelectedText.Result = ""
      ||
      Projektleiter.THIRDPROPERTY = FilterProjektTHIRDDROPDOWN.SelectedText.Result
    )
)

 

Replace Projektleiter.SECONDPROPERTY and Projektleiter.THIRDPROPERTY with the column names to filter by

Replace FilterProjektSECONDROPDOWN and FilterProjektTHIRDDROPDOWN with the control names.

 

Please give that a try and let me know how that works for you - I use similar multiple dropdown filters  in my Apps,

 

Cheers,

Sancho


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Helper I
Helper I

Re: Ignore Filter when IsBlank

This version works for me, thanks a lot!

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,023)