Answered

Filtering large dataset, delegation warning for multi-choice column

(1) Share

Report

Posted on by OneWinPlease

155

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

developermct 37 on at

Like (0)

Report

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. 😊👍👍

Was this reply helpful? Yes No
Ami K 15,679 Super User 2024 Season 1 on at

Like (1)

Report
@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.

Was this reply helpful? Yes No
OneWinPlease 155 on at

Like (1)

Report

@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.

Was this reply helpful? Yes No
OneWinPlease 155 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Ami K 15,679 Super User 2024 Season 1 on at

Like (1)

Report

@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"

Was this reply helpful? Yes No
OneWinPlease 155 on at

Like (2)

Report

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!

Was this reply helpful? Yes No
OneWinPlease 155 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Verified answer

Ami K 15,679 Super User 2024 Season 1 on at

Like (1)

Report
@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 ) ) )

Was this reply helpful? Yes No
OneWinPlease 155 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Ami K 15,679 Super User 2024 Season 1 on at

Like (0)

Report

@OneWinPlease - correct.

"_previous_30_days" or "_pre_filtered_data" are named values I have completely made up, but will reference later in the formula. If you work with Excel, this is similar in concept to a named formula.

You learn more about the With function here:

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-with

Was this reply helpful? Yes No