Why filtering by date still exceeds the list view threshold?

Question

I try to prepare a view for a list containing more than 5k items. When I try to filter it by an equal value in one column via REST API /Items?$filter=Status eq 'Not completed' , it does show a list of items in the response.

I understand that it is successful because the number of the items after filtering is lesser than 5. However, I then try to apply another filter:

/Items?$filter=StatusChangeDate gt datetime'2022-03-01T11:38:55Z'

I receive the error The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. It seems strange because the number of the items after applying the filter should definitely not exceed 5k. It is as if for the date the server regarded the number of items before filtering, not after. But if that is so, I do not understand why it does work with the first filter.

What could be the reason behind those filters being treated differently and is there any other way to apply filtering by date and not exceed this threshold?

Answer 1

I don't see where can be the problem, why don't you append the Filter into one expression !

like this :

/Items?$filter=(Status eq 'Not completed') and (StatusChangeDate gt datetime'2022-03-01T11:38:55Z')

This should work perfectly !!

Answer 2

I know this is kinda late but is it helps anyone. I found the solution as to index the column on SharePoint. Because the column is indexed it doesn't need to load all the rows first.

https://stackoverflow.com/questions/40362465/sharepoint-online-rest-api-for-list-calling-filters-and-limit-the-return-items