Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Conditionally Filter a Table Based on a Drop Down

(0) ShareShare
ReportReport
Posted on by 37

I have a Data Table with Items being pulled from a Table (not a collection). I am trying to use a drop down with the following values ["","Sales","Human Resource"] to conditionally filter the table. Code works correctly if Sales or Human Resources is selected but shows nothing with the blank "" value is selected. My filter code:

Filter(
 Master,
 txt_EmployeeColumnFilter.Text in 'Employee Name',
 If(dd_DeptColumnFilter.SelectedText.Value <> Blank(),
 dd_DeptColumnFilter.SelectedText.Value in Dept
 )
)

What I want is if the drop down value selected is empty/NULL/blank then the Dept filter is not applied. Tried many combinations of this and each time I select "" no records are displayed in the table.

  • RandyHayes Profile Picture
    RandyHayes 76,287 on at
    Re: Conditionally Filter a Table Based on a Drop Down

    @fmc1 

    Yes, collections are WAY over used!!  They are needed only when you need an in-memory database/table that allows you to add, remove and change records in memory.  There are other situations where they are needed, but they are rare and yet everyone seems to want to use them.

    A collection is just a table and it, as mentioned above, allows you the add, remove, and change ability.  

    The WORST part of collections is that you cannot find where they are defined or altered.

    A variable on the other hand, can also be a table.  A variable is static and is only changed when you change it in a formula.  However, especially in your case, a department list is probably not something you need to add, remove or change records in memory for.  So the variable is preferred (if a variable at all is needed) because it will not have the overhead of the add, remove, and change logic in the app.  It will just be the table.  PLUS, you CAN search on variables and find where they are defined and used.

    Ultimately, it is better to just get the data from the datasource as it is already a "sort of" collection in the app.  But that choice depends a lot on how dynamic things need to be.

     

    So, just keep in mind - collections are tables that are editable.  Variables can be anything, including tables and records.  Datasources are the source of the data and are always tables.

     

    I hope this is clear and helpful.

     

  • fmc1 Profile Picture
    fmc1 37 on at
    Re: Conditionally Filter a Table Based on a Drop Down
    I wanted to use Table but did not think I could include a filtered table. I guess I need to find some good documentation on the differences in performance and overhead using collections, tables and data source tables/lists. Most of what I read talks about using collections and at first, they appeared more flexible to work with.
     
    Thanks for the information.
  • RandyHayes Profile Picture
    RandyHayes 76,287 on at
    Re: Conditionally Filter a Table Based on a Drop Down

    @fmc1 

    Your formula has a mismatch of columns.  You are specifying a value column in the first record and then adding records that have a Result column from the Distinct function.  These will not compare.

     

    I would skip the collection for this...It is overkill and adds to performance drags in your app!

    If you want a more dynamic way, then set your Items property to:

    Ungroup(
     Table(
     {Items: Table({Result:""}) },
     {Items: Sort(Distinct(Master,Dept),Result)}
     ),
     "Items"
    )

    This will provide an accurate Items property to your control and will add the blank to the Distinct list of the Dept records.

     

    Why this over the collects?  

    1) It does not drag your app with collections

    2) It will be completely dynamic

    3) If you even need to maintain the Items property, it will be in one place - no need to see that it is based on a collection and then try to figure out where the collection is set (you cannot search for collections and see where they are defined in PowerApps...so you're left with trying to figure it out on your own)

     

    IF you want to make it more based on the OnStart of the app, then use a Variable NOT a collection.  For the main point of number 3 above.  You CAN search on Variables, you cannot search on collections.

    So, in your OnStart:

    Set(glbDepartments,
     Ungroup(
     Table(
     {Items: Table({Result:""}) },
     {Items: Sort(Distinct(Master,Dept),Result)}
     ),
     "Items"
     )
    )

    Then change your Items property of the Dropdown to : glbDepartments

     

    At least with that, if you ever need to maintain something about the Items property, you will see it is a variable and can then just look that up to see where it is defined...again, can't do that with a collection!

  • fmc1 Profile Picture
    fmc1 37 on at
    Re: Conditionally Filter a Table Based on a Drop Down

    Both solutions worked. But then I thought I would make the dept values dynamic based on the underlying data:

    ClearCollect(deptValues,{value:""},Sort(Distinct(Master,Dept),Result,Ascending));

    I run the following on startup and it works perfectly. At some point the list of departments will come from its own table but for now this will work.

  • Verified answer
    RandyHayes Profile Picture
    RandyHayes 76,287 on at
    Re: Conditionally Filter a Table Based on a Drop Down

    @fmc1 

    This is because you changed the column name.

    When you use: ["","Sales","Human Resource"] This syntax denotes a table of records.  Since the column name is not provided, PowerApps will assume Value for the column name.

    A collection is overkill for what you are doing as it is an in-memory database object, I would simply set a variable instead.

    If you want to keep the Filter formula the same, then use the following:

    Set(deptValues,
     Table(
     {Value: ""},
     {Value: "Sales"},
     {Value: "Human Resources"}
     )
    )

    Then set your Items property to : deptValues

     

    If you want to specify the column differently:

    Set(deptValues,
     Table(
     {deptVal: ""},
     {deptVal: "Sales"},
     {deptVal: "Human Resources"}
     )
    )

    Then you need to change the column in your formula to:

    Filter(Master,
     txt_EmployeeColumnFilter.Text in 'Employee Name' &&
     (IsBlank(dd_DeptColumnFilter.Selected.deptVal) ||
     dd_DeptColumnFilter.Selected.deptVal in Dept
     )
    )
  • fmc1 Profile Picture
    fmc1 37 on at
    Re: Conditionally Filter a Table Based on a Drop Down

    That fixed the problem and now I understand the logic on filters. But, if I remove my hard coded drop down dept values ["","Sales","Human Resources"] and replace it with a collection that includes the same values, the data table again shows no results. Collection is the following:

    ClearCollect(
     deptValues,
     {deptVal: ""},
     {deptVal: "Sales"},
     {deptVal: "Human Resources"}
    )

    I updated my drop down Items = deptValues. They show correctly but break the data table filtering. 

  • zmansuri Profile Picture
    zmansuri 6,048 on at
    Re: Conditionally Filter a Table Based on a Drop Down

    Filter( Master, txt_EmployeeColumnFilter.Text in 'Employee Name',
    Dept = dd_DeptColumnFilter.SelectedText.Value || dd_DeptColumnFilter.SelectedText.Value = "All")

     

    Add All in your dropdown:

     

    ["All","Sales","Human Resource"]

  • RandyHayes Profile Picture
    RandyHayes 76,287 on at
    Re: Conditionally Filter a Table Based on a Drop Down

    @fmc1 

    Please consider changing your Formula to the following:

     

    Filter(Master,
     txt_EmployeeColumnFilter.Text in 'Employee Name' &&
     (IsBlank(dd_DeptColumnFilter.Selected.Value) ||
     dd_DeptColumnFilter.Selected.Value in Dept
     )
    )

     

     

    I hope this is helpful for you.

     

    EDIT: The above formula has been changed as I noticed you were using SelectedText in it.  That is a deprecated property and should be avoided.  It is corrected above.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard