Think twice when using functions to filter your data sets in the Canvas Apps

Here is a warning message which I keep running into:

(The “Filter” part of this formula might not work correctly on large data sets)

It’s not that I keep running into it every day. But I find myself looking at this warning every time I’m setting up data sources for a new application.

So, maybe, if I write it a few times here, I’ll remember to do it right from the start the next time I’m doing it.

Think twice when using functions in the filter conditions

Think twice when using functions in the filter conditions

Think twice when using functions in the filter conditions

Think twice when using functions in the filter conditions

Now, if you are new to this, and if this does not make a lot of sense so far, let me explain.

Canvas Apps are lazy – they know how to delegate work  to the data sources. For example, if I wanted to find all accounts that are called “Big Corp”, ignoring the character case, I could use Filter function like this:

As a result, I’d get that “Big Corp” account. Yet, there would be no delegation warning.

This is because, for this kind of straightforward condition, Canvas App would just delegate filtering work to the data source – instead of loading all accounts and filtering them on the client, all that work will be happening on the “server” (in this particular case, it means CDS Web API service would be doing the filtering).

Not everything can be delegated, though. Actually, there are only a few basic functions/operators which are delegate-able:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

By the way, I’m not going to speculate why “Upper” would not be delegatable for SQL data source either. There is a corresponding MS SQL “Upper” function, so, it seems, this might have been  delegateable… But it’s not.

There is one scenario where we can use “non-delegatable” functions in the conditions. It’s when those functions are applied not to the “columns”, but to the constant values ( to the variables, for example):

In this case, Canvas App would know that it can calculate Upper(“big corp”) beforehand, then delegate the rest of filtering work to the data source.

Hope this makes sense so far?

How about this one, then:

Compare the last two screenshots. Can you tell why, in the last example, there is no data that matches the condition, whereas in the previous example “Big Corp” account showed up in the results?

PS. You are welcome to reply in the comments or on LinkedIn if that’s how you landed here