Answered

Filtering Data Source before Delegation

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have a Microsoft lists table with approximately 10 000 rows. To filter this in Powerapps, I have multiple drop downs that are dependent on prior ones. This is the items from the last dropdown:

Distinct(Filter('Table', Col1 = Dropdown1.SelectedText.Value, Col2 = Dropdown1_1.SelectedText.Value, 'Col3' = Dropdown1_2.SelectedText.Value, 'Reporting Period' = Dropdown1_4.SelectedText.Value), Col4)

The issue is the gallery being filtered by these dropdowns doesn't include all the rows since Col4 is multiple lines of text. My initial understanding was that as long as the first 3 columns filter the data source to below 2000 rows, I will receive all matching rows regardless of col4 being undelegable. Is this not the case? and are there any workarounds?

Gallery Items:

Filter('Table',Col1= Dropdown1.SelectedText.Value, Col2 = Dropdown1_1.SelectedText.Value, Col3 = Dropdown1_2.SelectedText.Value, Col4 = Dropdown1_3.SelectedText.Value)

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous ,

Unfortunately, if a non-delegable function appears in the formula, it will only retrieve the first x records.

If the number of filter outputs is less than or equal to 2000, please try to save the results in a collection and then use the distinct function.

If the num >2000, please consider using flow handling the large data. The idea is to pass the condition to flow, and then flow will return the processed outputs to the the canvas app.

Best Regards,

Wearsky

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

thanks for the response!
So, if I have a delegable filter function that returns 1 row, row number 10 000 and add a non-delegable filter to this function, the function will return nothing?

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous ,

If the record is the first record in the table then it will return a record else it will return empty.

Hope it helps.

Best Regards,

Wearsky

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

After further testing, I've found that filter(table, delegable function, undelegable function) is not equivalent to filter(filter(delegable functions), undelegable function), which fixes my issue. Nesting the filters allows the undelegable function to return all rows as long as the delegable functions return <= 2000 rows

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Anonymous ,

After further testing, I came to the same conclusion as you. Glad to see you solved the problem. 🙂

Best Regards,

Wearsky

Was this reply helpful? Yes No