Filtering in a Search Function

[Asdetevo]

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!

[v-bofeng-msft · Accepted solution]

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

[Asdetevo]

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