Unanswered

Get the distinct values from the data source having more than 2000 records.

(0) Share

Report

Posted on by Radhika123

Hi ,
I would like to get the below approach, please help me in this.
I have a share point list1 having columns "project","module","behavior", and the data source have more than 2000 records, now in power app I have combo box1 for project, and combo box 2 for module, now in combo box 3 I need to get all the distinct values of "behavior" based on combox1 and combox2.

Thanks in Advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Radhika123 - you can use the Filter function here. So long as you can be certain that the number of rows returned from filtering your list on the "project" and "module" fields return less than 2000 rows (the max delegation limit), you will be fine to use a non-delegable function like Distinct on that smaller subset of data.

Distinct( Filter( 'Your List', project = ComboBox1.Selected.Value, module = ComboBox2.Selected.Value ), behavior )

Note the above assumes the data type for the "project", "module", and "behaviour" fields are Single Line Text.

Was this reply helpful? Yes No
Radhika123 59 on at

Like (0)

Report
Copy link

Link copied!

Hi @Amik ,
Thanks for your reply.
By using "Distinct" it won't filter more than 500 records right , As mentioned I have data more then 2000 records. I need to do this for the data more than 2000 records.

Thanks
Radhika

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Radhika123 - the Distinct function is not delegable beyond 2000 rows. However, the Filter function is delegable.

Because the above code is running the Distinct function run on a filtered subset of data, as long as the output returned from filtering on the "Project" and "Module" fields (from ComboBox1 and ComboBox 2) returns less than 2000 rows, you will be fine to use a non-delegable function.

Was this reply helpful? Yes No
Radhika123 59 on at

Like (0)

Report
Copy link

Link copied!

Thanks @Amik
I'll try and get back to you

Was this reply helpful? Yes No
Radhika123 59 on at

Like (0)

Report
Copy link

Link copied!

Hi @Amik ,
I have tried the formula you have given, but it's not fetching all the values , i have 17 unique values , but it only showing 9 values instead. I think it's taking first 500 records for each 'Module'.
Previously I have used the below formula ,it's also giving the same 9 values .
"
GroupBy(Filter('Behaviour_And_Requirement''s',IsBlank(DataCardValue18.Selected.Value) || Module = DataCardValue18.Selected.Value,IsBlank(DataCardValue2.Selected.Value) || Project_Name = DataCardValue2.Selected.Value),Behaviours, GroupedBehaviours)"

Thanks
Radhika

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Radhika123 - is the output of that Filter returning less than 500 rows? (assuming that is the setting for your delegation limit).

Was this reply helpful? Yes No
Radhika123 59 on at

Like (0)

Report
Copy link

Link copied!

Hi @Amik ,
Yes, My data source is Share Point List ,So that's why the delegation is there for data row limit. But if I set the row limit to 2000 then my app will take more time to load.

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Radhika123 - I don't think you understood my question. To rephrase, does the output of this Filter return more than 500 rows?

Filter( 'Your List', project = ComboBox1.Selected.Value, module = ComboBox2.Selected.Value )

Was this reply helpful? Yes No
Radhika123 59 on at

Like (0)

Report
Copy link

Link copied!
@Amik
No, the output won't be more than 500 rows. The case is I need to return distinct values from "Behavior" column based on "project" & "module" Columns.
with the formula you have mentioned earlier ("
Distinct( Filter( 'Your List', project = ComboBox1.Selected.Value, module = ComboBox2.Selected.Value ), behavior )
")
It's giving only 9 values for particular filter, but the distinct values for the particular filter is 17 values.

Thanks
Radhika

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Radhika123 - interesting. What happens if you use:

With( { _pre_filtered_data: Filter( 'Your List', project = ComboBox1.Selected.Value, module = ComboBox2.Selected.Value ) }, Distinct( _pre_filtered_data, behavior ) )

Was this reply helpful? Yes No