Filter function does not work all the time

(0) Share

Report

Posted on by _deeplearn93

Hello All,

I am quite new to Powerapps and have noticed a bug which I am unable to solve.

I have a sharepoint excel with KPIOwner and KPI as columns. There could be multiple owners for the same KPI in which case there would be multiple rows for the same KPI ex.

KPIOwner	KPI
User A	OTD Asia
User B	OTD Asia
User B	OTD Americas

I am using KPIOwner as a filter to filter out the Gallery where I am displaying KPI and it's value (value comes from a different list which also has KPI as a column).

On some occassions, I am not able to see all the KPI that belong to an owner. There are instances where it may only return OTD Asia for user B and not OTD Amercias (based on example seen above).

This is the formula I am using- Filter('Source,Title = 'Week DD_1'.Selected.Value,KPIDesc=KPIDesc in ForAll(Filter(Table1,Text(KPIowner)='Week DD_3'.Selected.Value),KPI)

Thanks in advance

edited: My column name is KPI and not KPIDesc. I have corrected this now. Sorry for the confusion

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

mmbr1606 14,605 Super User 2025 Season 2 on at

Like (0)

Report
hey @_deeplearn93

can you please try this:

Filter( 'Source', 'Title' = 'Week DD_1'.Selected.Value, KPIDesc in Filter( Table1, Text(KPIOwner) = 'Week DD_3'.Selected.Value ).KPIDesc )

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.

Greetings

Was this reply helpful? Yes No
Yogesh Murugan 443 Moderator on at

Like (0)

Report

Filter(
Source,
Title = 'Week DD_1'.Selected.Value &&
KPIDesc in Filter(
Table1,
Text(KPIOwner) = 'Week DD_3'.Selected.Value
).KPIDesc
);
try this

Was this reply helpful? Yes No
Verified answer

BCBuizer 22,505 Super User 2025 Season 2 on at

Like (1)

Report
Hi @_deeplearn93 ,

The issue you have is caused by a lack of ability to delegate your filter conditions to the data source. As a result, only the first 500 items (by default) will be visible in your app. As a quick, but temporary fix, you can raise this number to 2000 under the App settings:

More information on delegation can be found here: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/delegation-overview

A common workaround is to use pre-filtering where first a delegable filter is used to bring the number of items below the Data Row Limit before applying the non-delegable filter, for instance:

With( {preFilter: Filter( 'Source', 'Title' = 'Week DD_1'.Selected.Value )}, Filter( preFilter, KPIDesc in Filter( Table1, Text(KPIOwner) = 'Week DD_3'.Selected.Value ).KPIDesc ) )

Was this reply helpful? Yes No
_deeplearn93 12 on at

Like (0)

Report

Getting an error with this formula that KPIOwner is not recognizable

Was this reply helpful? Yes No
_deeplearn93 12 on at

Like (0)

Report

Getting an error with this formula

Was this reply helpful? Yes No
_deeplearn93 12 on at

Like (0)

Report

Hello. Thank you for the detailed explanation. It is already set to 2000 rows. The prefilter formula works for some records but does not work for some others...

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report

Hi @_deeplearn93 ,

In that case the issue is probably the use of Excel as a datasource. You might consider migrating to a more forgiving data source.

Was this reply helpful? Yes No
_deeplearn93 12 on at

Like (0)

Report

Thank you so much for your help. This solution seems to have worked for now as I am getting all the rows I am expecting. Although it is still showing delegation warning on the text part of the formula. any function I could replace it with ?

Was this reply helpful? Yes No