cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fmourtaza
Helper II
Helper II

Filter DataTable based on DropDown value

Hello mate,

 

We have got a requirement to filter a DataTable based on TextBox, please note that the Search has to be in line with the selected column in the dropdown and the column in the collection.

 

Example1: User select dropdown [Name], therefore search should perform only against column displayName in the collection. 

Example12 User select dropdown [Department], therefore search should perform only against column Department in the collection.

 

If I call the code directly like this, it works

- DataTable > Items: Filter(FinalResult, StartsWith(Text(Department), TextInput1.Text)) - this works as Department is a directly coming from the Collection

 

However when I try the following:

- DropDown > OnChange: Set(VarSelection, Dropdown1.SelectedText.Value);

- DataTable > Items: Filter(FinalResult, StartsWith(Text(VarSelection), TextInput1.Text)) - this doesnt work.

 

fmourtaza_0-1603706778463.png

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions

@fmourtaza ,

Looking back to the start, you want to select which matches, so

If(
   Dropdown1.Selected.Value = "Name",
   Filter(
      FinalResult, 
      StartsWith(
         displayName,
         TextInput1.Text
      )
   ),
   Filter(
      FinalResult, 
      StartsWith(
         Department,
         TextInput1.Text
      )
   )
)

 

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.

View solution in original post

8 REPLIES 8
WarrenBelz
Super User III
Super User III

Hi @fmourtaza ,

I am not sure exactly what you are trying to do here, but if you wanted the Department to match TextInput1 and the name to match Dropdown1.Selected.Value (and you set the Variable as in your example - this may be the pattern you are looking for

Filter(
   FinalResult, 
   StartsWith(Department, TextInput1.Text) &&
   Name=VarSelection
)

 

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.

 

Hello @WarrenBelz 

 

Thanks for your quick reply.

 

The Department or Name or var1 has to be interchangeable, in this approach the value is coming from the DropDown therefore I set it in a variable;   

 

Filter(FinalResult, StartsWith(Text(Department or Name or var1 or var2...), TextInput1.Text))

@fmourtaza ,

You do not need the Text reference - it is already Text. I am still a bit unclear, but if you want

  • Department (or even the start of it) to match TextInput1 OR
  • Name to match Dropdown1.Selected.Value

then

Filter(
   FinalResult, 
   StartsWith(Department, TextInput1.Text) ||
   Name=Dropdown1.Selected.Value
)

Is this what you are trying to do?

 

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.

 

@fmourtaza ,

Looking back to the start, you want to select which matches, so

If(
   Dropdown1.Selected.Value = "Name",
   Filter(
      FinalResult, 
      StartsWith(
         displayName,
         TextInput1.Text
      )
   ),
   Filter(
      FinalResult, 
      StartsWith(
         Department,
         TextInput1.Text
      )
   )
)

 

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.

View solution in original post

It works amazingly 🙂 

 

A last query: when I select Department - the entries which are not having any data are filtered automatically - any way to avoid that ? 

 

fmourtaza_0-1603713659843.png

 

@fmourtaza ,

I have re-configured the post (a bit of a long way, but it will work for all cases) to get rid of some potential Delegation issues, but if it works for you (I assume you are filtering a collection), go with it. 

One small problem with StartsWith() is it does exactly what you are seeing initially as an empty search box will show empty records. Try making the Default of the Text Box "" (empty string) OR

If(
   Dropdown1.Selected.Value = "Name",
   Filter(
      FinalResult, 
      StartsWith(
         displayName,
         TextInput1.Text
      ) && Len(displayName)>0
   ),
   Filter(
      FinalResult, 
      StartsWith(
         Department,
         TextInput1.Text
      )  && Len(Department)>0
   )
)

 

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.

I updated with your latest code and the results for Department is same - am I missing something ? 😞

 

@fmourtaza ,

Try this then - it will still have Delegation issues possibly

If(
   Dropdown1.Selected.Value = "Name",
   Filter(
      FinalResult, 
      StartsWith(
         displayName,
         TextInput1.Text
      ) && displayName <> Blank()
   ),
   Filter(
      FinalResult, 
      StartsWith(
         Department,
         TextInput1.Text
      )  && Department <> Blank()
   )
)

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,813)