Delegation warning has disappeared, but in fact the problem is still there

[martin42]

Hello everybody,

I have an expression (thanks to @Scott_Parker - Solved: Re: How to nest two loops to build a table in Powe... - Power Platform Community (microsoft....) that works fine, but as the data grows I now encounter a delegation problem. The app is in Teams environment and the tables in Dataverse.

there is a list of people called "_DptUsersList", and the dates "_firstDayOfMonth", "_lastDayOfMonth" and "_dateSelected"

With(
    {
        PlanningDateTable: ForAll(
            Sequence(Day((_lastDayOfMonth)) As xDay,
            {
                PlanningDate: Date(
                    Year(_dateSelected),
                    Month(_dateSelected),
                    xDay.Value
                )
            }
        )
    },
    ClearCollect(
        _VistaMese,
        ForAll(
            _DptUsersList As xPerson,
            {
                Name: xPerson.User_Name,
                Value: ForAll(
                    PlanningDateTable As xDate,
                    {
                        Value: LookUp(
                            Filter(
                                PLADM_Planning, 
                                Planning_Date = xDate.PlanningDate
                            ), 
                            User_ID.User_Name = xPerson.User_Name).Presence_ID.Presence_Code
                    }
                )
            }
        )
    )
)

It seemed to me that all functions may be delegated, but there is a warning in the last part of the code 

So I tried to create a collection with the data already filtered by date and department, so as to reduce the rows to no more than 400 at worst, and then point to the collection instead of the PLADM_Planning table.

ClearCollect(
    TempTable;
    Filter(
        PLADM_Planning;
        And(
            Planning_Date >= _firstDayOfMonth;
            Planning_Date <= _lastDayOfMonth;
            User_ID.User_Name in _DptUsersList.crf0b_name
        )
    )
)

The delegation warning disappeared, but in reality the problem is not solved: if I go into Settings and lower the Limit for rows of data to 1, then the result is incomplete.

This is my first app and I'm not a IT guy, and I'm still a little confused about all this.

What else could I try?

[martin42]

Maybe I'm making an issue out of nothing: if the size of the TempTable does not exceed 500 rows, then I should have no delegation problems.

Is that right?

[BCLS776]

It's not your collection "TempTable" size that will cause row-limit issues - it is the amount of rows requested from the table(s) as part of your queries that will run into row limits. For example, if a Filter() contains a non-delegable query and the table has more rows than the row limit, you'll get an incomplete query. Working with delegation is also key to creating performant apps.

I would need a much deeper understanding about what you're trying to achieve with your app to help you build something that is delegable as much as possible. A table generation using a ForAll() is typically not delegable (see documentation), but there might be other ways to achieve this. With Dataverse, a powerful feature is the ability to create table Views, which are generated at the database level, and then query those as a pre-filtered version of your original table. Views can include fairly complex queries that reference related fields.

Hope that helps,

Bryan

[martin42]

Hi Bryan,
thank you very much for the explanation; so to recap:

• I have an expression with a delegation warning
• then I create a collection to filter the data and reduce the number of rows below the limit
• now Intellisense no longer shows the delegation warning, but that doesn't mean that the query is now delegable
• it actually remains non-delegable, even though the warning is no longer there
• as long as the collection is within the row limit, then the query is complete and the table will have all the correct data

Is this correct?

[martin42]

I'll explain why I use this table:
in the homepage there's a gallery, associated with table Views, one per department, to show the attendance for the day for each department; it is working fine, people are happy.
But they also want to see the data for the whole month in an "Excel-like" table. Old habits die hard...
I know that from a performance point of view it is really poor, but at the moment it is enough for me that it works.

Thanks again,

Paolo

[BCLS776]

---

@martin42 wrote:

Hi Bryan,
thank you very much for the explanation; so to recap:

• I have an expression with a delegation warning
• then I create a collection to filter the data and reduce the number of rows below the limit
• now Intellisense no longer shows the delegation warning, but that doesn't mean that the query is now delegable
• it actually remains non-delegable, even though the warning is no longer there
• as long as the collection is within the row limit, then the query is complete and the table will have all the correct data

Is this correct?

---

Your last point might not work in all situations. If creating a collection requires the app to query 2500 rows without delegation, then you will see incomplete results even if the resulting collection is 100 rows in size. The row limit applies to the length of the data returned to the app for further processing, not the end result.

To your second point, once your desired data is within the app in a collection, then delegation doesn't matter. You can operate on 5000 rows of a collection without worry of incomplete data.

A strategy some use to work with queries that are somewhat larger than the delegation limit (several thousand rows, but not 10s of thousands for performance reasons) is to load all of those items into a collection once at app startup and then filter as needed from there. Again, once those items are in an internal collection, delegation doesn't matter. This works well for data that does not change frequently, or you can re-create the collection at reasonable intervals for good user experience.

Hope that helps,

Bryan

[martin42]

Bryan, I really appreciate your help but I am still not sure I understand the underlying logic.
To give a concrete example:

• In the OnSelect property of a button, I first create a collection by applying a Filter on a Dataverse table. So this first operation is delegable.
• The table is 20k rows, but with the filters the collection becomes 300 rows.
• Next, I apply a non-delegable formula to the collection, to create a monthly table.

Will the monthly table be complete with all the data?

thanks again

Paolo

[BCLS776 · Accepted solution]

---

@martin42 wrote:

Bryan, I really appreciate your help but I am still not sure I understand the underlying logic.
To give a concrete example:

• In the OnSelect property of a button, I first create a collection by applying a Filter on a Dataverse table. So this first operation is delegable.
• The table is 20k rows, but with the filters the collection becomes 300 rows.
• Next, I apply a non-delegable formula to the collection, to create a monthly table.

Will the monthly table be complete with all the data?

thanks again

Paolo

---

Yes, as long as your initial Filter on the Dataverse table is delegable, you'll have no issues past that point working directly on the collection.

Bryan

[martin42]

you don't know how relieved I am to hear you say that, I'm buying everybody beers tonight 😁