Cascading distinct dropdowns in large SharePoint list that have assigned default values (Collections vs. column filter)

[DublinOH_User]

First - thank you for your time.

These columns are all in the same a single SharePoint table that has 8000 rows - so delegation is a factor

We have a gallery that we need to 3 dropdowns to filter a gallery in any combination

1. Each should filter based on the other two's selected value
• (Column 1 sets distinct/filtered choices for Column 2 & Column 3,  - Column 2 sets distinct/filtered choices for Column 1 & Column 3, and so on & so on)
2. There needs to be a default value in each dropdown list so the gallery can show all results 
3. There also needs to be a condition that if only one of the dropdowns is selected the other two dropdowns filter and are distinct but, no action in the gallery except the selected value filter

All three collections for dropdowns are set similar (currently on app start)

ClearCollect(collectPlantsOvNx,{Result: "ALL PLANTS"});Collect(collectPlantsOvNx,Sort((Distinct('PF_PM_ Compliance_OverNext30','PLANT CODE')),"PLANT_x0020_CODE",Ascending));

'DrpdwnPlantCd_PM' items are set to collectPlantsOvNx

This gallery items works:

//Default - All dropdowns set to CLEAR or default

Filter('PF_PM_ Compliance_OverNext30',

'SERVICE COMPLETED DATE' = Blank(),

//Selected Plant with selected PM and selected Units

Or('PLANT CODE'=DrpdwnPlantCd_PM.Selected.Result,DrpdwnPlantCd_PM.Selected.Result="ALL PLANTS"),

Or('UNIT#'=DrpdwnUNIT_PM.Selected.Result,DrpdwnUNIT_PM.Selected.Result="ALL UNITS"),

Or('NAME OF PM'=DrpdwnName_PM.Selected.Result,DrpdwnName_PM.Selected.Result="ALL PM TYPES"))

ISSUE

When collections are used so we can establish default values, we can't filter or create the distinct cascade in the other dropdowns based on the first selection.

So when the First dropdown for Plants is selected  - the other two dropdowns are cascading. The choices in the remaining dropdowns do not make sense and cause blank results in the form connected to the gallery confusing the end-user.

When I tried filtering the table column - I did not know how add a default value so when the 'clear' dropdowns was selected the gallery shows all items

Is there are better approach?

Or

How do we make the dropdown collection results dependent on one another?

[DublinOH_User]

There are no errors when I use that in the dropdown item property - BUT the only item in the drop-down is the default, there are no other items in the dropdown list

[Drrickryp]

@DublinOH_User 

It's hard to tell whether it will work without having the data.  Give it a shot and see how it goes. If you want, I have another model for a contact list that uses a multiselect combobox for states and a dropdown for cities.  I agree with your group about giving users the least amount of leeway in making choices. 

[DublinOH_User]

The formula has no errors but the dropdowns are blank

[Drrickryp]

@DublinOH_User 

I think the problem is putting text in the dropdown formula. As a proof of concept, instead of putting something like "All" in the formula for the dropdowns, use Blank() instead.  The screenshots show a primary dropdown (region) and a dependent dropdown (country) as well as a multiselect combobox for Item Type.  The first screenshot shows the table with nothing selected in the dropdowns.

The screenshot below shows the dropboxes and combobox populated.

The final screenshot shows the formulas that I used in the above examples

[DublinOH_User · Accepted solution]

This is how I was able to filter and add a default text value

It may require some thought as you set each dropdown items property. If too many conditions are added it causes a circle reference error - so keep that in mind while laying out dependencies.

Note:

Column1 in my example is a collection - use (.Result)/column2 is just a column - use (.Value)
Text values can be substituted with Blank() as in the suggestions above in this string

SOLUTION EXAMPLE placed in the ITEMS properties of each dropdown (varied names depending on the dropdown):
With(
{
_items: Distinct(
Filter(
'YourTableName,

//Each condition is matching column or collection with its corresponding dropdown
Or('Column1'=DrpdwnName1.Selected.Result,DrpdwnName1.Selected.Result="TEXT VALUE"),
Or('Column2'=DrpdwnName2.Selected.Value,DrpdwnName2.Selected.Value="Text Value")
),
'ColumnNameforDistinct'
)
},
ForAll(
Sequence(CountRows(_items) + 1),
{
Value: If(
Value = 1,
"TEXT VALUE", //same as the one you set above
Last(
FirstN(
_items,
Value - 1
)
).Result) } ) )

Gallery items being filtered by
One regular column filter/2 columns with the above solution /plus one additional column set to a collection:

//Default - All dropdowns set to CLEAR or default
Filter(
'YourTableName',
//Addtional condition to what we needed
'Column4' = Blank(),

//Fitler Selected item from collection (DrpdwnPlantCd_PM has its items set to a collection) with selected item column 2 and selected column 3 (their dropdown items were set with the above example)
Or('PLANT CODE'=DrpdwnPlantCd_PM.Selected.Result,DrpdwnPlantCd_PM.Selected.Result="ALL PLANTS"),
Or('UNIT#'=DrpdwnUNIT_PM.Selected.Value,DrpdwnUNIT_PM.Selected.Value="ALL UNITS"),
Or('NAME OF PM'=DrpdwnName_PM.Selected.Value,DrpdwnName_PM.Selected.Value="ALL PM TYPES")
)

Hope this helps someone!
Big thank you to the person who helped me above for pointing me in the right direction!!!