Data Table Filtering - Delegation Warnings

[ImageZ]

hey,

I've tried to do this many many different ways and have done a lot of research just cant see to make it stick fully.

Basically have an excel online connector (has to be this unfortunately) which feeds a power apps canvas data table, on this data table I want to have multiple filters (they wont always all be used so cant follow aleft to right kind of flow)

I've gotten as close as the code below, however the 500 delegation warning applies UNLESS the first box (agentsearchbox2.text) has a value in, which it wont always have - much appreciated.

With({searchvar1:Filter(Submissions,StartsWith(Agent,AgentSearchBox_2.Text)))},
With({searchvar2:Filter(searchvar1,StartsWith('Created By',AuditorSearchBox_2.Text)))},
With({searchvar3:Filter(searchvar2,StartsWith(State,StatusSearchBox_2.Text)))},
With({searchvar4:Filter(searchvar3,StartsWith(FormName,FormSearchBox_4.Text)))},
With({searchvar5:Filter(searchvar4,StartsWith(UniqueID,IDSearchBox_5.Text)))},
With({searchvar6:Filter(searchvar5,Value(PublishedDateNum)>=Value(DateDiff(DateValue("31/12/1899")-1,DatePicker1.SelectedDate))))},
With({searchvar7:Filter(searchvar6,Value(PublishedDateNum)<Value(DateDiff(DateValue("31/12/1899")-1,DatePicker1_1.SelectedDate))))},

Filter(SearchingList,Agent<>Blank()))))))))

[WarrenBelz]

Hi @ImageZ ,

I am not sure what the logic is in the last two filters, but assuming it gets what you need

With
   (
      _DateVal:
      Value(
         DateDiff(
            DateValue("31/12/1899")-1,
            DatePicker1.SelectedDate,
            TimeUnit.Days
         )
      )
   },
   Filter(
      SearchingList,
      (
         Agent = Blank() ||
         (   
            StartsWith(
               Agent,
               AgentSearchBox_2.Text
            )
         ) &&
         StartsWith(
            State,
            StatusSearchBox_2.Text
         ) &&
         StartsWith(
            FormName,
            FormSearchBox_4.Text
         ) &&
         StartsWith(
            UniqueID,
            IDSearchBox_5.Text
         ) &&
         Value(PublishedDateNum)>= _DateVal &&
         Value(PublishedDateNum) < _DateVal
      )
   )
)

Please also set the Default of all the Search boxes to "" (empty string)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[ImageZ]

Hey mate,

Still receiving the delegation limit, specifically around the "Or" operator 😞

[WarrenBelz]

@ImageZ 

I just noticed (I missed it before) that you are using Excel. Sorry, but all bets are off on Delegation - it is a terrible data source (this video may be useful for you) and things simply do not work.

[ImageZ]

No problem mate, if I move the table to the dataverse - that will allow multi filters correct?

[Talha_Dar]

Hi @ImageZ,

If you can move your data to Dataverse, then you can use the search function directly, as the search function is delegable in Dataverse.

If you found my answer helpful, please consider giving it a thumbs-up or a like. Your feedback is greatly appreciated!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WarrenBelz]

@ImageZ ,
SharePoint is a very viable option (and it requires no additional licencing cost as Dataverse does).

[ImageZ]

As in a sharepoint list? I intitialy tried that but found the reporting from a list to PowerBi extremely slow.

[WarrenBelz]

@ImageZ ,

I do not know why (although I am no PowerBI expert). I use nothing but SharePoint and am yet to find something it could not handle.

[ImageZ]

It was the refreshes we found to be extremely slow vs any other data connection we use. ill do some digging on the licenseing for dataverse. I have the premium but do all end users require it too?