cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Asdetevo
Microsoft
Microsoft

Filtering in a Search Function

Hi All, 

 

I have a search function that works off of an IF statement and a dropdown, where IF a sales person's name is selected from a dropdown, it filters our database on their specific deals, and if no sales person's name is selected, it shows all deals present. I need to add another filter to allow sales people to only see the "open" deals they're working, using a separate dropdown, and I'm having trouble combining the formulas. The search function is as follows: 

 

Search(If(DealConsultantDropdown.SelectedText.Value="Select an option",

               SortByColumns('[DWR].[vwCases_Innovation_App_Triage]',"Date",Descending),

               SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value),"Date",Descending)

         )

       ,SearchBox.Text,"CustomerName","CaseId"

       )

 

and my filter function on just the dropdown is this: 

 

If(Dropdown_1.Selected.Value = "Open Cases", Filter('[DWR].[vwCases_Innovation_App_Triage]','Case Ask Status' = "Open")

How do I combine these two to retain full functionality? Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

HI @Asdetevo

Do you want to filter the data source in this rule?

Case1: DealConsultantDropdown.SelectedText.Value="Select an option" && Dropdown_1.Selected.Value = "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]','Case Ask Status' = "Open"),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case2: DealConsultantDropdown.SelectedText.Value<>"Select an option"&&Dropdown_1.Selected.Value = "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value && 'Case Ask Status' = "Open"),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case3: DealConsultantDropdown.SelectedText.Value="Select an option"&&Dropdown_1.Selected.Value <>"Open Cases"

 

Search(SortByColumns('[DWR].[vwCases_Innovation_App_Triage]',"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case4: DealConsultantDropdown.SelectedText.Value<>"Select an option"&&Dropdown_1.Selected.Value <> "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

If so, I suggest you try this code(Nesting of If functions)

 

Search(
If(
  Dropdown_1.Selected.Value = "Open Cases",
  Filter(       /*If Dropdown_1.Selected.Value = "Open Cases", filter out the records that meet the 'Case Ask Status' = "Open") condition and records*/
   SortByColumns(
    If(
     DealConsultantDropdown.SelectedText.Value="Select an option",  /* Nesting of If functions */
     '[DWR].[vwCases_Innovation_App_Triage]',
     Filter(
      '[DWR].[vwCases_Innovation_App_Triage]',
      DCName=DealConsultantDropdown.SelectedText.Value)
      )
     ,"Date",Descending
    ),
   'Case Ask Status' = "Open")
  ,
  SortByColumns(
   If(                                                        /* Nesting of If functions */
    DealConsultantDropdown.SelectedText.Value="Select an option",
    '[DWR].[vwCases_Innovation_App_Triage]',
    Filter(
     '[DWR].[vwCases_Innovation_App_Triage]',
     DCName=DealConsultantDropdown.SelectedText.Value)
     )
    ,
   "Date",Descending)
  )
,SearchBox.Text,"CustomerName","CaseId"
)

 

Best Regards,

Bof

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

HI @Asdetevo

Do you want to filter the data source in this rule?

Case1: DealConsultantDropdown.SelectedText.Value="Select an option" && Dropdown_1.Selected.Value = "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]','Case Ask Status' = "Open"),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case2: DealConsultantDropdown.SelectedText.Value<>"Select an option"&&Dropdown_1.Selected.Value = "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value && 'Case Ask Status' = "Open"),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case3: DealConsultantDropdown.SelectedText.Value="Select an option"&&Dropdown_1.Selected.Value <>"Open Cases"

 

Search(SortByColumns('[DWR].[vwCases_Innovation_App_Triage]',"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

Case4: DealConsultantDropdown.SelectedText.Value<>"Select an option"&&Dropdown_1.Selected.Value <> "Open Cases"

 

Search(SortByColumns(Filter('[DWR].[vwCases_Innovation_App_Triage]',DCName=DealConsultantDropdown.SelectedText.Value),"Date",Descending),SearchBox.Text,"CustomerName","CaseId")

 

If so, I suggest you try this code(Nesting of If functions)

 

Search(
If(
  Dropdown_1.Selected.Value = "Open Cases",
  Filter(       /*If Dropdown_1.Selected.Value = "Open Cases", filter out the records that meet the 'Case Ask Status' = "Open") condition and records*/
   SortByColumns(
    If(
     DealConsultantDropdown.SelectedText.Value="Select an option",  /* Nesting of If functions */
     '[DWR].[vwCases_Innovation_App_Triage]',
     Filter(
      '[DWR].[vwCases_Innovation_App_Triage]',
      DCName=DealConsultantDropdown.SelectedText.Value)
      )
     ,"Date",Descending
    ),
   'Case Ask Status' = "Open")
  ,
  SortByColumns(
   If(                                                        /* Nesting of If functions */
    DealConsultantDropdown.SelectedText.Value="Select an option",
    '[DWR].[vwCases_Innovation_App_Triage]',
    Filter(
     '[DWR].[vwCases_Innovation_App_Triage]',
     DCName=DealConsultantDropdown.SelectedText.Value)
     )
    ,
   "Date",Descending)
  )
,SearchBox.Text,"CustomerName","CaseId"
)

 

Best Regards,

Bof

View solution in original post

Thank you so much, I had this most of the way but I was struggling nesting my functions. This seems to be working great!

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 (1,664)