Error when filtering using the IN function

(0) Share

Report

Posted on by Beatriz Araújo

I have the following code when I run it and the following error appears: "Error when trying to retrieve data from the network : Number of link entity: 0 Number of link filter: 28 : exceeded limit 27 is report = false" Could anyone help me resolve the error?

Sum(
 Filter(
 Col_Resumo;
 Pedido.'ID Compra' <> Blank() &&
 !IsBlank('Data do Lançamento') &&
 Pedido.'ID Compra' in 
 Filter(
 Col_Pedido;
 'Status da Compra' = Opt_Status_Compra_5200.Encerrado &&
 'Planejamento de Compra'.ID_Plan_Compra_5100 in 
 Filter(
 Col_Planej;
 !IsBlank(ID_Plan_Compra_5100)
 ).ID_Plan_Compra_5100
 ).'ID Compra'
 );
 Value('Subtotal Custeio (A1)')
);

Imagem do WhatsApp de 2024-05-24 à(s) 16.58.11_b6b4f985.jpg

Imagem do WhatsApp de 2024-05-24 à(s) 16.59.52_b4001f2d.jpg

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

MichaelFP 2,001 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Have you opened the monitor to check query api? it looks like that amount filter "in" has reach out the limit. Maybe it is good to review you query because filter could get multiples lines and you query could be getting to bigger to be retrieved.

Was this reply helpful? Yes No
Beatriz Araújo 7 on at

Like
a
(0)

Report
Copy link

Link copied!

Então, eu estou usando uma collect filtrando uma em outra, em pouca quantidade de registros, funciona muito bem, mas em grande quantidade ele dar erro. A collect tem mais de 8000 k de registros, fiz um loop pegando todos os registros, mas também não posso usar for all filtrando a collect dando um apelido e usando ela no lugar do IN, porque demora muito também.

Was this reply helpful? Yes No
MichaelFP 2,001 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Grandes dataset de dados vai ter baixa performance no canvas app. Recomendo você aplicar mais um filtro e ir fazendo paginação desse dados.

Se ele for um dado que é carregado somente uma vez e útilizado para leitura, vale a pena usar cache para melhorar a performance.

Was this reply helpful? Yes No
Suggested answer

PR-27010821-0 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey,

this subject is pretty old, but I still wanted to help other people reading this. Basically I get that error when I use the "in" operator on a lookup column in dataverse with a collection greater than 27. It only appears on lookup columns. You can use the in operator with a collection with way more entries than 27 on text or number columns in dataverse!

What I did to fix it:

Create a formula column within your dataverse table and reference the value of the lookup column that you need to filter. Example: If you have a lookup column called "Department" and you you want something like "Department.Name" just reference "Departmen.Name" in the formula column and name the new column like you want. In my example I use "DeparmentNameFlatValue".

Go into PowerApps and filter "DepartmentNameFlatValue" in "CollectionGreater27Items".

Hope that helps some people.

Was this reply helpful? Yes No
timl 37,283 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for sharing that @PR-27010821-0.

Hopefully that will be of use to someone else with this issue.

Was this reply helpful? Yes No