Filtering large dataset, delegation warning for multi-choice column

[OneWinPlease]

I have a help desk gallery connected to a large SharePoint list, over 2000 items. I'm trying to filter it based on my Choices column called Location, but I'm getting a delegation warning. My goal is to use a Dropdown box to let users filter the gallery.  The formula on the Items property of the gallery that comes closest is 
     Filter('Service Request List', Location = Dropdown1.Selected.Value.Text)

but I still get the delegation warning. We have 50 locations, and my SharePoint list Location column accepts multiple values. So I can't use the Distinct function. I also can't use the In function because the list is over 500 records. Any other ideas?  

I saw a suggestion somewhere of trying to add another filter to the filter, but many of our fields are choice-based, and Location is the primary way users would search for existing tickets at their site. Maybe I could filter by Location plus any ticket less than a month old, but I'm not sure how to write the formula for both of these. 

[developermct]

If you use choice columns, you're going to have a bad time with delegation. I would recommend creating a calculated column based on your choice's text. If I ever feel the itch to create a choice column, I create a separate table or I will create a collection in the App.Formulas space. 

I hope that helps! 😊👍

https://youtu.be/5mHjbzQefcw?si=LmlP9PlgRj75Rxlv

---

Darren Neese, Power Apps MVP
➡️Get access to my free Power Apps crash course here: https://go.superpowerlabs.co/superlanding

---

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up. 😊👍👍

---

[Amik]

@OneWinPlease - 

You're correct in that you will need to pre-filter your list with the delegable Filter function. As long as you can be certain that your filtered data will return less than 500 rows (or max 2000 if changed via settings), you can safely run your non-delegable operation on that smaller subset of data.

In your scenario, you will need to be certain that filtering your data within the last month will return less than 2000 rows.

For example, we can leverage the With function to return a data within the last 30 days of a Date column, and then apply the non-delegable In function on that filtered subset. 

With(
    {
        _previous_30_days: DateAdd(
            Today(),
            -30,
            TimeUnit.Days
        )
    },
    With(
        {
            _pre_filtered_data: Filter(
                'Your List',
                YourDateColumn >= _previous_30_days
            )
        },
        Filter(
            _pre_filtered_data,
            Len(ComboBox1.Selected.Value) = 0 || ComboBox1.Selected.Value in 'Your Multi Choice Column'.Value
        )
    )
)

Notes:

• Although your Choice field in your SharePoint list is set to multi-select, the above assumes your ComboBox control is set to single-select.
  
  
• Your Date column must be Date only. The above is not be delegable on a DateTime data type.

[OneWinPlease]

@Amik  thank you for your reply.  As the list grows, with 50 locations potentially putting in tickets over time, a pre-filtered filter wouldn't return less than 500 rows for very long, I'm afraid. Your point about the Date column means I would have to change its format, as currently it has the time as well, but that shouldn't be a big deal. (famous last words).  I sure appreciate you taking the time to write out a formula for me.

[OneWinPlease]

@developermct , thanks - I will watch your video. I don't know how to do the things you mentioned and will be glad to learn.

[Amik]

@OneWinPlease - this comment of yours seems to suggest you will return less than 500 rows if you pre-filter your list for records less than a month old...

"Maybe I could filter by Location plus any ticket less than a month old"

[OneWinPlease]

Yes, you're right @Amik  - I think at the end of a work week I wasn't thinking clearly when I responded. (I was doubtful I could "guarantee" anything.)  I will try your formula on Monday and report back here!

[OneWinPlease]

@Amik , for various reasons I can't make the Date/Time column just Date. I don't think I have another column in this list that wouldn't eventually exceed 500 items. (I thought about the Status column but next year some of my locations could very well have more than 500 rows for "completed" status.)  So I am back to square one. I may just create a view that is not filtered, and let users see all tickets put in at any time. 

[Amik · Accepted solution]

@OneWinPlease - I was not being clear in my post.

The key point is that comparing a Date Only field with a DateTime field is not delegable because they are different data types. If your Date field is a DateTime then use Now() instead of Today():

With(
    {
        _previous_30_days: DateAdd(
            Now(),
            -30,
            TimeUnit.Days
        )
    },
    With(
        {
            _pre_filtered_data: Filter(
                'Your List',
                YourDateTimeColumn >= _previous_30_days
            )
        },
        Filter(
            _pre_filtered_data,
            Len(ComboBox1.Selected.Value) = 0 || ComboBox1.Selected.Value in 'Your Multi Choice Column'.Value
        )
    )
)

[OneWinPlease]

Ah ok, @Amik  - thanks for clarifying. May I ask one more thing: is your expression _pre_filtered_data: actually the real expression here?  I don't want to assume but, if I replace my list, my date/time column, and the name of my two comboxes, I should use everything else in your formula verbatim?