Power Fx delegation of 'in' operator against Davaverse

Delegation of queries in Canvas Apps/Custom Pages has long been a troublesome topic and we are always looking out for the triangle of doom, or the double blue underline of eternal stench (well, it is Halloween soon!)

I try to keep a close eye on the connector delegation support table in the official documentation for any changes and additions. Part of what I love about the Power Platform is that new features are constantly being released, often without fanfare! 

Here is the current delegation support at the time of writing (for posterity):

The Caveats are important - especially around aggregation limits:

1. Numeric with arithmetic expressions (for example, Filter(table, field + 10 > 100) ) aren't delegable. Language and TimeZone aren't delegable.

2. Doesn't support Trim[Ends] or Len. Does support other functions such as Left, Mid, Right, Upper, Lower, Replace, Substitute, etc.

3. DateTime is delegable except for DateTime functions Now() and Today().

4. Supports comparisons. For example, Filter(TableName, MyCol = Blank()).

5. The aggregate functions are limited to a collection of 50,000 rows. If needed, use the Filter function to select 50,000

6. CountRows on Dataverse uses a cached value. For non-cached values where the record count is expected to be under 50,000 records, use CountIf(table, True).

7. For CountRows, ensure that users have appropriate permissions to get totals for the table.

Old 'in' delegation limit

The really exciting addition to this table is the mention of 'In (membership)'. It is currently marked as preview but can be used in the latest version of canvas studio.

Previously, if you had written a formula to get all the accounts that had a primary contact of A or B it might look like:

Set(varInFilter, 
    [
        First(Contacts).Contact, 
        Last(Contacts).Contact
    ]);

ClearCollect(colAccounts,
    Filter(Accounts, 'Primary Contact'.Contact in varInFilter)
);

In this situation, previously you would have been presented with the delegation warnings:

When you execute the query you would have seen the warning:

The reason being is that the query that was executed against Dataverse would be:

/api/data/v9.0/accounts?
$select=accountid,dev1_AccountStatus,primarycontactid,_dev1_accountstatus_value,_primarycontactid_value

Here there are no filters that are sent to the server to filter by the primary contact, so the delegation limit will be hit.

The new 'In' server-side delegation!

With the new behaviour, if you are using version 3.22102.32 or later (See all versions), the 'in' operator is not delegable to Dataverse. This means you will see no warning:

And inside the monitor, you see a clean delegated query!

This is because the filtering is now performed on the server using the OData query:

/api/data/v9.0/accounts?
$filter=(primarycontactid/contactid eq ... or primarycontactid/contactid eq ...)&$select=accountid,primarycontactid,_primarycontactid_value

The key part here is that the primarycontactid is filtered using the OR query. This is great news because we no longer will hit that delegation limit.

Those troublesome polymorphic relationships

One of the constant challenges in Power Fx is the support for polymorphic relationships in Dataverse when performing delegated queries. This new support is no exception, unfortunately. If you were to write the following formula you would still hit the delegation limit:

ClearCollect(colcontacts,
    Filter(Contacts, AsType('Company Name',[@Accounts]).Account in varInFilter)
)

I'm going to be keeping an eye out for this to be supported in the future and I'll let you know! 

Check out my video showing this new 'in' delegation when used with the Creator Kit! 

@ScottDurow