Unanswered

Distinct and Filter not working to populate combo box

(0) Share

Report

Posted on by EmilioatUplyft

I have read a number of posts explaining how to use Distinct + Filter and as far as I can tell, I'm implementing them correctly. Hopefully, someone can see something I'm not.

I have a SP List as a lookup table (intune_applications_naming_convention_nodes) with 3 main columns: |node_os|policy_context|application_name|

I'm trying to connect 3 combo boxes to the List, one for each column.

In the first column, I just grab Items as

Distinct(intune_application_naming_convention_nodes,node_os)

I get all the records for os returned. When I try to setup the second combo box, I get 2 empty items if I use Distinct

Distinct(Filter(intune_application_naming_convention_nodes, node_os = app_os.Selected.Result),policy_context)

but I get 3 visible items if I leave it out

Filter(intune_application_naming_convention_nodes, node_os = app_os.Selected.Result)

I'm not sure what I'm missing here.

second combo box filters items based on combo 1, but Distinct fails

not sure how to FIlter the items. for 3rd combo box based on previous two

Categories:

Building Power Apps

I have the same question (0)

All responses (16)

Answers (1)

mdevaney 29,989 Moderator on at

Like (2)

Report

Are you getting delegation warnings? I can see some warning icons near your combo boxes.

Keep in mind that DISTINCT is a non-delegable function. Only the first 500 records in your datasource will be read by PowerApps and from there it will return the unique values.

Perhaps your list is larger than 500 records. You can increase the delegation limit to 2000 rows in the advanced settings but again this only works if you keep the list within that size limit.

If you plan to exceed 2000 rows you might require a different approach altogether. Let me know

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (1)

Report
Here's what I am thinking:

Distinct(Filter(intune_application_naming_convention_nodes, node_os = app_os.Selected.Result),policy_context)
might be returning what appears to be 2 blank values may be because the display property isn't set properly. Make sure it matches the display property of the 1st combo box.

When you remove the Distinct, you see 3 values, but are those 3 unique? Or are there 2 out of those 3 that are the same?

Once we solve for this, the 3rd one can be solved for easily.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!
Hardit(Haman)

Was this reply helpful? Yes No
EmilioatUplyft 80 on at

Like (0)

Report
Thank you so much for your quick response, @PowerAddict!
I hadn't changed the default Visible property on my test app, but, I had on the form I'm trying to build so thank you for pointing out that Visible can affect combo items.

When I check the app now, the second combo box appears to be working with the Distinct?? 2 Unique items as opposed to 3 items with one duplicate. I'm not sure how to diagnose that as some changes seem to take effect right away... Additionally, sometimes when I check in the "Play" app in the builder I see one behavior and when I open the published app I see another...so confusing...

So this Items appears to work for combo 2:
Distinct(Filter(intune_application_naming_convention_nodes, node_os = app_os.Selected.Result),policy_context)

Now lets try the third combo box? I need to filter intune_application_naming_convention_nodes by both OS and Context and return just those application names that match for both.
combo 2 is working now. didn't change anything. how to set up combo 3?

Thank you very your feedback so far!

Was this reply helpful? Yes No
EmilioatUplyft 80 on at

Like (0)

Report

@mdevaney Good eyes, yes there is a delegation warning, but my SP List is currently 5 items and will never exceed 500. I'm not really sure how a delegation warning is treated by powerapps, but I assumed that since my SP List is less than 10, it wouldn't technically cause an error for me.

I don't understand how delegation works really for large datasets, I was hoping that was a problem for a later day!

Thank you kindly for replying!

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (1)

Report

Try this:
Filter(intune_application_naming_convention_nodes, node_os = app_os.Selected.Result, node_context = app_context.Selected.Result)

node_context is what I assumed is the name of the context column like how node_os is for OS
app_context is what I assumed is the name of the context dropdown

Let me know if this works.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!
Hardit(Haman)

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report

@EmilioatUplyft
Yes, you have made a good judgement call. This appears to be a case where you can safely ignore the delegation warnings.

--a short description of delegation just for you--

Whenever possible PowerApps will try to delegate query operations to Sharepoint. A function that is delegable is run against the entire dataset in Sharepoint and the result is output to PowerApps. Some functions are considered non-delegable because MS hasn't built the support yet. Non-delegable means the first 500 records are output to PowerApps and the function is run against that smaller subset of records. Using a non-delegable function means you risk having an incomplete result.

PowerApps will always give you a warning when a potential delegation issue is present. It can be dealt with in the following ways:
1. Ignore - when your dataset is not expected to exceed 500 rows (you can also bump up the limit to 2,000).
2. Write a delegable formula instead. - currently only some data types and functions are supported in SP
3. Change the SP List structure - whether by utilizing one-to-many relationships to work-around non-delegable functions or changing data types (although I am guessing all data types will eventually be supported).

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
EmilioatUplyft 80 on at

Like (0)

Report

Thank you kindly for the example @PowerAddict . I can't get it to work. I tried it with two different SP Lists of roughly the same structure. In both cases, I can get combo 2 working with Distinct 🙂
I tried combo 3 with your code suggestion, with and without Distinct and got empty items.
Multiple expressions in Filter()
Second SP List to test with

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report

Can you please post the exact formula you used since mine was assuming names which may have been incorrect?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Was this reply helpful? Yes No
EmilioatUplyft 80 on at

Like (0)

Report
Sorry.
I tried with (right side)

Distinct(Filter(intune_device_naming_convention_nodes, node_os=device_os.Selected.Result,policy_context=device_context.Selected.Result),property_clusters)

and without (left side)

Filter(intune_application_naming_convention_nodes, node_os=app_os.Selected.Result, policy_context = app_context.Selected.Result)

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report

No problem, thanks for sharing the data. I am going to recreate this and get back to you soon.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!
Hardit(Haman)

Was this reply helpful? Yes No