cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
EmilioatUplyft
Level: Powered On

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.

combo-cascade-3.pngsecond combo box filters items based on combo 1, but Distinct fails

 

combo-cascade-2.pngnot sure how to FIlter the items. for 3rd combo box based on previous two

 

1 ACCEPTED SOLUTION

Accepted Solutions
PowerAddict
Level 10

Re: Distinct and Filter not working to populate combo box

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

Re: Distinct and Filter not working to populate combo box

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.
PowerAddict
Level 10

Re: Distinct and Filter not working to populate combo box

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)

EmilioatUplyft
Level: Powered On

Re: Distinct and Filter not working to populate combo box

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

 

Thank you very your feedback so far!

EmilioatUplyft
Level: Powered On

Re: Distinct and Filter not working to populate combo box

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

PowerAddict
Level 10

Re: Distinct and Filter not working to populate combo box

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)

Super User
Super User

Re: Distinct and Filter not working to populate combo box

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

EmilioatUplyft
Level: Powered On

Re: Distinct and Filter not working to populate combo box

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. 

combo-cascade-5.pngMultiple expressions in Filter()

combo-cascade-6.pngSecond SP List to test with

PowerAddict
Level 10

Re: Distinct and Filter not working to populate combo box

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)
EmilioatUplyft
Level: Powered On

Re: Distinct and Filter not working to populate combo box

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,998)