Cascading dropdowns not returning all values

Like (0) Share

Report

Posted on 27 Jul 2021 11:16:21 by JimJim

289

Hi Guys,

I have two dropdown lists on my form, dd_Level1 and dd_Level2

I have a hierarchy of data and I am trying to create two dropdown lists. The first one selects all distinct values from column HierarchyLevel1, the second selects distinct values from column HierarchyLevel2 where HierarchyLevel1=dd_Level1

Here is the Items property for dd_Level1

Sort(Distinct([@PowerBIIntegration].Data,hierarchylevel1),Result)

Here is the Items property for dd_Level2

Sort(Distinct(Filter([@PowerBIIntegration].Data,hierarchylevel1=dd_Level1.Selected.Result),hierarchylevel2),Result)

My problem is that dd_Level2 only returns 2 values (one of which is a blank value) and it should be returning about 12 different values.

As you can see I am integrating my app within PowerBI

My form looks like this:

Annotation 2021-07-27 121503.jpg

Please help 😃

Categories:

General Questions

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

JimJim 289 on 02 Aug 2021 at 08:04:42

Like (0)

Report

Re: Cascading dropdowns not returning all values

Hi Guys, In the end I worked out that it was the distinct function that was preventing delegation. I had to copy the first level (distinct values) of the hierarchy into a separate list, this allowed me to select all of the values on dropdown 1, then I was able to filter the level 2 values based on the level 1 dropdown.

Was this reply helpful? Yes No
JimJim 289 on 28 Jul 2021 at 08:07:39

Like (0)

Report

Re: Cascading dropdowns not returning all values

We aren't talking about lots of data here, there are 4741 rows in total

Distinct values:
Level1: 6
Level2: 49
Level3: 368
Level4: 1230
Level5: 4741

Was this reply helpful? Yes No
KvB1 1,596 on 28 Jul 2021 at 05:45:57

Like (0)

Report

Re: Cascading dropdowns not returning all values

I don't know =/

How many distinct values in level1 and level2 are we talking about?

Was this reply helpful? Yes No
JimJim 289 on 27 Jul 2021 at 14:29:14

Like (0)

Report

Re: Cascading dropdowns not returning all values

Thank you @KvB1 ,
I believe this is the issue, the Level1 items that appear before POW (alphabetical order) have a complete list of Level2 values available, the data is getting cut off in POW somewhere which explains why values are missing. There is another Level1 value (TEM) which is also missing.
Instead of pulling through all of my data and then filtering it, I think I need to apply the filter to the Power BI data model and only return the data I need. Is this possible?

Was this reply helpful? Yes No
KvB1 1,596 on 27 Jul 2021 at 14:07:16

Like (1)

Report

Re: Cascading dropdowns not returning all values

Hi,

Your filters are looking good. The reason you use .Result instead of .Value, is because the items in your dropdown are the result of the Distinct value.

The row limit indicates how many rows are able to be retrieved by PowerApps in a single query. The default value is 500, so if your filter query would return 501 records, only the first 500 are retrieved to powerapps. You can set this to a maximum of 2000 in the settings in your app.

However, this would assume that the first 500 records of your filter only contain a blank or POWAP for the column you are showing, that seems unlikely. My suggestion would be to troubleshoot, try to test parts of your filter function. For example, you could replace the filter phrase from dropdown.result to just hardcoding something to test whether using the dropdown as a filter is causing the issue.

Was this reply helpful? Yes No
JimJim 289 on 27 Jul 2021 at 13:51:48

Like (0)

Report

Re: Cascading dropdowns not returning all values

Further update: I believe the issue is that PowerBIIntegration.Data is not returning all of my data, is there a way to get around this? The table in question only has about 4500 rows

Was this reply helpful? Yes No
JimJim 289 on 27 Jul 2021 at 12:06:24

Like (0)

Report

Re: Cascading dropdowns not returning all values

I used Shane Young's video to get this far, I notice in order to filter on the previous dropdown he uses Selected.Value, but Value isn't available to me which is why I used Selected.Result. I'm not even sure if this is what the issue is

Was this reply helpful? Yes No
Powerplatform01 386 on 27 Jul 2021 at 11:55:46

Like (0)

Report

Re: Cascading dropdowns not returning all values

Hi @JimJim ,
You are missing something,No worries you can watch this video to resolve your issue.
https://youtu.be/Jq_qSZv98OI
Request you to please like and subscribe this video

Was this reply helpful? Yes No