Power Apps Filtering Issue Beyond 2000 Records: Seeking Alternative Solutions

[Gowtham_Tamilan]

I have the following Power Apps code, which was functioning correctly until the table's record count reached 2000. However, beyond this threshold, the filtering operation fails to consider records beyond the 2000th entry. Can anyone propose an alternative approach to address this issue effectively? here ses_usr_tID.Value is acollection and holding team IDs. 

ClearCollect(
colTeamGoalsbyTeamID,
Filter(
Fact,
Team_ID in ses_usr_tID.Value && Goal_Deleted<>true
)
);

ClearCollect(
    ses_usr_tID,
    Filter(
       Teams,
        Team_Owner_Emp_ID = sessionUserEMPL_ID || Team_Member_EMPID = sessionUserEMPL_ID 
    )
);
ClearCollect(
    ses_usr_tID,
    Distinct(
        ses_usr_tID,
        Team_ID
    )
);
ClearCollect(
    colTeamGoalsbyTeamID,
    Filter(
        Fact,
      Team_ID in ses_usr_tID.Value  && Goal_Deleted<>true
          )
);

[365-Assist]

Perhaps build your collection in parts. e.g. First filter and Collect adds records for Users whose name starts from A-M. then a Second filter and Collect where User Names start from N-Z.

---------------------------------------------------
Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

Subscribe: https://www.youtube.com/channel/UCFpvUlpx84FuIPOdInGKMTw

Regards
Darren Lutchner - 365 Assist

[WarrenBelz]

Hi @Gowtham_Tamilan ,

This may be a stop-gap solution depending on how many records will have Goal_Deleted as false

With(
   {
      _Data:
      Filter(
         Fact,
         Goal_Deleted = false
      )
   },
   ClearCollect(
      colTeamGoalsbyTeamID,
      Filter(
         _Data,
         Team_ID in ses_usr_tID.Value
      )
   )
);

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Gowtham_Tamilan]

Just checking, will this approach work efficiently if the filter condition matches more than 2000 records? The condition should iterate over the table, and in this case, it's expected to match 5000 records:

Filter(
Fact,
Goal_Deleted = false
)

[WarrenBelz · Accepted solution]

@Gowtham_Tamilan ,

It can query 5,000 records (or any number), but can only return up to the first 2,000 where Goal_Deleted = false

Your problem is that you then need to apply a non-delegable filter, so this needs to be done "locally" (by the app) and the maximum number of records available is restricted by your Data Row Limit maximum of 2,000. How many records are in ses_usr_tID ?

This may break something running and still has a restriction of 2,000 items for each team, but try

Clear(colTeamGoalsbyTeamID);
ForAll(
   ses_usr_tID As _Team,
   Collect(
      colTeamGoalsbyTeamID,
      Filter(
         Fact,
         Team_ID = _Team.Value &&
         Goal_Deleted = false
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Gowtham_Tamilan]

The Fact table has 10000 records,  after adding a filter Goal_Deleted=false ,total count is 8000.

ses_usr_tID collection has 5 records. I need to check the table where ses_usr_tID has matches in 8000 records

[WarrenBelz]

@Gowtham_Tamilan ,

I have amended the code posted, but you still have a maximum of 2,000 per team match and it will take some time to run if it succeeds. 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Gowtham_Tamilan]

This is working fine and thanks for your time & effort.