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

[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.

[Amik]

@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.

[Radhika123]

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

[Amik]

@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.

[Radhika123]

Thanks @Amik 

I'll try and get back to you 

[Radhika123]

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

[Amik]

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

[Radhika123]

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.

[Amik]

@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
    )

[Radhika123]

@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