Loading...

Power Fx delegation of 'in' operator against Davaverse

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):

Item Number [1] Text [2] Choice DateTime [3] Guid
Filter Yes Yes Yes Yes Yes
Sort Yes Yes Yes Yes -
SortByColumns Yes Yes Yes Yes -
Lookup Yes Yes Yes Yes Yes
=, <> Yes Yes Yes Yes Yes
<, <=, >, >= Yes Yes No Yes -
In (substring) - Yes - - -
In (membership) (preview) Yes Yes Yes Yes Yes
And/Or/Not Yes Yes Yes Yes Yes
StartsWith - Yes - - -
IsBlank Yes [4] Yes [4] No [4] Yes [4] Yes
Sum, Min, Max, Avg [5] Yes - - No -
CountRows [6] [7], CountIf [5] Yes Yes Yes Yes Yes

 

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

Published on:

Learn more
Develop 1 - Dynamics 365 Architecture Services
Develop 1 - Dynamics 365 Architecture Services

Share post:

Related posts

Power FX in Power Pages

The Microsoft team keeps adding new features and functionality to Power Pages. Just announced, the ability to use Power FX in Power Pages! See...

1 month ago

Using Power Fx on Power Apps command bar

If you're looking to enhance the functionality of your model-driven Power Apps, one way to do so is by adding custom commands to the command b...

1 month ago

Most used PowerFX in Canvas APP List

If you're working with Power Apps to create Canvas apps, then understanding the most important Power Fx list functions is critical for managin...

2 months ago

Commonly used PowerFX Functions in Custom Pages Power Platform

Power Fx is a powerful formula language that is designed for creating dynamic and interactive user experiences in Power Apps. This guide highl...

2 months ago

Commonly used Power Fx Functions for Model-Driven Apps

If you're working with Model-Driven Apps, you may have noticed that Power Fx takes a backseat compared to Canvas Apps due to the heavy relianc...

2 months ago

Power FX Copilot

Power FX Copilot is the latest addition to the Power Apps Studio, providing a new efficient approach to writing formulas. This feature is desi...

2 months ago

Power Fx Formula Columns in Microsoft Power Apps

In this article, the author explores the concept of Power Fx Formula Columns in Microsoft Power Apps. If you're looking to enhance your knowle...

2 months ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy