Delegation issue when filtering a Dataverse gallery by date and user

#Issue

A Canvas App uses Dataverse as its backend data source. A gallery must filter records by both a date and a user. The user field is a lookup to the Dataverse user table, and Power Apps displays a delegation warning when the two conditions are combined.

Delegation warnings matter because Power Apps may evaluate only the first portion of the data locally. If the data source contains more records than the app's data row limit, the gallery can show incomplete or incorrect results.

#Recommended approach

Use simple comparisons directly against Dataverse columns. Resolve the current user once, create a start and end boundary for the selected date, and combine the predicates with &&.

#1. Resolve the current user

Create a variable when the app starts, or when the screen becomes visible. Replace Email and UserId with the actual logical or display names from the Dataverse user table in your environment.

Set(
    varCurrentUser,
    LookUp(
        Users,
        'Primary Email' = User().Email
    )
)

For a Dataverse owner lookup, the Users table may be named Users, and the user identifier may be the Dataverse system user ID. Confirm the exact table and column names in the data source panel.

#2. Create date boundaries

If the Dataverse column contains a date and time, do not wrap the column in DateValue, Text, or another transformation. Instead, compare the original column with the beginning of the selected day and the beginning of the next day:

Set(varSelectedDateStart, DateValue(dteSelectedDate.SelectedDate));
Set(varSelectedDateEnd, DateAdd(varSelectedDateStart, 1, TimeUnit.Days))

The range includes all records on the selected date without requiring a calculation on the Dataverse column itself.

#3. Apply the composite gallery filter

Set the gallery's Items property to a direct Dataverse filter. For a custom user lookup column named AssignedTo, use:

With(
    {
        selectedDateStart: DateValue(dteSelectedDate.SelectedDate),
        selectedDateEnd: DateAdd(
            DateValue(dteSelectedDate.SelectedDate),
            1,
            TimeUnit.Days
        )
    },
    Filter(
        Requests,
        AssignedTo = varCurrentUser &&
        RequestDateTime >= selectedDateStart &&
        RequestDateTime < selectedDateEnd
    )
)

Replace Requests, AssignedTo, and RequestDateTime with the actual Dataverse table and column names.

For a Dataverse Owner lookup, the pattern is similar:

Filter(
    Requests,
    Owner = varCurrentUser &&
    RequestDateTime >= DateValue(dteSelectedDate.SelectedDate) &&
    RequestDateTime < DateAdd(
        DateValue(dteSelectedDate.SelectedDate),
        1,
        TimeUnit.Days
    )
)

The important pattern is equality on the lookup and range comparisons on the unmodified date column.

#Avoid non-delegable patterns

The following patterns commonly cause delegation warnings or incomplete results:

// Transforms the data-source column
Filter(Requests, DateValue(RequestDateTime) = dteSelectedDate.SelectedDate)

// Converts the lookup or date to text
Filter(Requests, Text(AssignedTo.Name) = cmbUser.Selected.Name)

// Searches a lookup display name instead of comparing the lookup
Filter(Requests, StartsWith(AssignedTo.Name, txtUserSearch.Text))

// Loads the full table into a local collection before filtering
ClearCollect(colRequests, Requests)

Instead, filter the Dataverse column directly. If a user-search experience is required, use a delegable search pattern supported by the target Dataverse column, or filter by a user identifier selected from a delegable user picker.

#If the user is selected from a combo box

When the user chooses a record from a combo box, compare the lookup to the selected user record rather than comparing display text:

Filter(
    Requests,
    AssignedTo = cmbUser.Selected &&
    RequestDateTime >= varSelectedDateStart &&
    RequestDateTime < varSelectedDateEnd
)

Do not use cmbUser.Selected.Name unless the data source column is a text column. A Dataverse lookup should be compared with the selected lookup record or a supported identifier.

#Troubleshooting steps

1. Confirm that Requests is connected directly to Dataverse and is not a local collection.
2. Check the delegation warning underline and identify the exact expression that Power Apps cannot delegate.
3. Test the date predicate by itself.
4. Test the user lookup predicate by itself.
5. Combine the two predicates with && only after each individual predicate works.
6. Remove DateValue, Text, Value, First, Last, and nested LookUp calls from the Dataverse-side predicate where possible.
7. Set the data row limit temporarily to a small value and test records beyond that limit. The result should remain correct if the filter is delegated.
8. Confirm that the user record belongs to the same Dataverse environment as the app.
9. Check the user's table permissions and row-level access.
10. Verify that the date column's time-zone behavior is understood. A Dataverse DateTime value may display in local time while comparisons use the stored value.

#Beginner validation procedure

1. Add several test rows for the same user on the selected date.
2. Add a row for a different user on the same date.
3. Add a row for the selected user on a different date.
4. Select the first date and user. Only the matching rows should appear.
5. Change the date. The gallery should update without loading the entire table.
6. Change the user in the combo box. Only that user's rows should appear.
7. Test with more records than the app's data row limit.
8. Confirm that the delegation warning is gone, or that any remaining warning applies only to a separate non-delegable expression.

#Notes about lookup fields

• A lookup display name is not the same as the lookup record.
• Compare a lookup to a Dataverse user record or supported ID, not to a formatted name string.
• Avoid filtering on a related display field such as AssignedTo.Name when direct lookup equality is sufficient.
• If the app requires a complex related-table search, consider storing a searchable text or identifier column in the primary table, while keeping the lookup as the authoritative relationship. Confirm the design and security implications before adding a denormalized column.

#Additional information

• Canvas Apps render incorrectly on mobile devices
• Understand delegation in a canvas app
• Delegable functions and operations for Dataverse