cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nusserdt
Level: Powered On

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
GarethPrisk
Level: Powered On

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
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. 

GarethPrisk
Level: Powered On

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




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
Nusserdt
Level: Powered On

Re: Ignore Filter when IsBlank

This version works for me, thanks a lot!

Helpful resources

Announcements
thirdimage

New Badges

Check it out!

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

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