Filtering Dropdown Menu Based On Other Dropdown Menus

[AaronBreen]

Hey folks, hoping what I want to do is possible and simple.

I have several drop down menus that have filtered options based on the previous drop down selection.

ddTechnologyFilter
Choices('DRAKE Scenarios'.Technology)

ddOrderTypeFilter
Distinct(Filter('DRAKE Scenarios',Technology.Value = ddTechnologyFilter.Selected.Value), 'Order Type'.Value)

ddNewExisting
Distinct(Filter('DRAKE Scenarios','Order Type'.Value = ddOrderTypeFilter.Selected.Value), 'New or EXISTING Device'.Value)

Code for the first 3 drop downs (and the drop down name), all the columns are single choice fields. 

My problem comes from the 4th drop down which is a list of multi choice fields, so the data is no longer just a value, but a table of values (which is how I understand it, and also where my issue is). So each row in my SharePoint list will have multiple choices for this column.

My first attempt at just repeating my previous code didn't work, but I sort of expected that.

ddDeviceType
Distinct(Filter('DRAKE Scenarios','New or EXISTING Device'.Value = ddNewExisting.Selected.Value), 'Device Type'.Value)

I thought I might need to run a Filter on a Choices like this, but it didn't work and I've been stuck.

ddDeviceType
Filter(Choices('DRAKE Scenarios'.'Device Type'),'New or EXISTING Device'.Value = ddNewExisting.Selected.Value)

I have got my gallery functioning perfectly based on this drop downs selection if I just use a simple Choices, but it shows values that won't be applicable (and the gallery correctly shows nothing) but I would like to avoid that.

ddDeviceType
Choices('DRAKE Scenarios'.'Device Type')

[narayan225]

@AaronBreen 

What error do you get when you use the above formula?

Can you try without .value when filtering the data?

Cheers!

[AaronBreen]

Says it doesnt recognise that column for some reason.

[WarrenBelz]

Hi @AaronBreen ,

Assuming that is a multi-select combo box querying a multi-choice field, try this

Distinct(
   Ungroup(
      ForAll(
         ddNewExisting.SelectedItems As _Sel,
         Filter(
            'DRAKE Scenarios',
            _Sel.Value' in 'New or EXISTING Device'.Value 
         )
      ),
      "Value"
   ),
   'Device Type'.Value
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[AaronBreen]

It's a drop down querying multi-choice field, in this scenario, there was no reason for the end user to make more than 1 selection.

I did try your code with a combo box though, and got this error, plus it was just showing "Items" (renamed everything properly).

[WarrenBelz]

@AaronBreen ,

Are you saying this is single selection combo box with a multi-choice field?

Distinct(
   Filter(
      'DRAKE Scenarios',
      dNewExisting.Selected.Value in 'New or EXISTING Device'.Value 
   ),
   'Device Type'.Value
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[AaronBreen]

They're all just drop downs, not combo boxes.

All are connected to single choice columns, except for this one (ddDeviceType) which is a multi choice column.

[WarrenBelz]

@AaronBreen ,

That image you have posted is a Combo Box (not a drop-down), however the code I posted should work.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[AaronBreen]

Sorry yeah. I meant that I was using a drop down, then changed it to a combo box for your code.

Doesn't seem to like it as a drop down or combo box.

[WarrenBelz]

@AaronBreen ,

It does not really matter which one (drop-down or combo box) . however the code I provided was based on what I interpreted from your post

Distinct(
   Filter(
      'DRAKE Scenarios',
      dNewExisting.Selected.Value in 'New or EXISTING Device'.Value 
   ),
   'Device Type'.Value
)

• You have a List called 'DRAKE Scenarios'
• It has a multi-choice field called 'New or EXISTING Device'
• You have a drop-down called NewExisting containing possible values in the field above
• You want the Distinct values of a (Single) Choice field called 'Device Type'

If all of that is true, the code should work - so what is not correct ?