cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Distinct and Filter not working to populate combo box

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 failssecond 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 twonot sure how to FIlter the items. for 3rd combo box based on previous two

 

1 ACCEPTED SOLUTION

Accepted Solutions

Attached is a document that shows this step-by-step. It works perfectly fine, please let me know if it doesn't work for you. 

 

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

Hardit(Haman)

View solution in original post

15 REPLIES 15
Super User III
Super User III

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.
Super User II
Super User II

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)

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?combo 2 is working now. didn't change anything. how to set up combo 3?

 

Thank you very your feedback so far!

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

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)

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

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()Multiple expressions in Filter()

Second SP List to test withSecond SP List to test with

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)

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)

 

 

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (7,079)