Cascading dropdowns not returning all values

(0) Share

Report

Posted on 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:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Powerplatform01 386 on at

Like
a
(0)

Report
Copy link

Link copied!

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
JimJim 289 on at

Like
a
(0)

Report
Copy link

Link copied!

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
JimJim 289 on at

Like
a
(0)

Report
Copy link

Link copied!

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
KvB1 1,596 on at

Like
a
(1)

Report
Copy link

Link copied!

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 at

Like
a
(0)

Report
Copy link

Link copied!

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 at

Like
a
(0)

Report
Copy link

Link copied!

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 at

Like
a
(0)

Report
Copy link

Link copied!

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
Verified answer

JimJim 289 on at

Like
a
(0)

Report
Copy link

Link copied!

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