Filter Gallery with Multiselection Combo Box where all Items Meet Criteria

[ayattaw]

I have poked around on the forum but have not yet managed to find my scenario. 

I have a multiselection combo box and a gallery. The combobox Items are"

Distinct(Filter('List A', 'Archived?'.Value ="No", 'Dropdown1'.Selected.Value="All Options"||'Dropdown1'.Selected in ColumnX),Title)

 

I need my Gallery to filter List B, where all of the combobox selections return true. I currently have:

 

Sort(Filter(

'List B',

Status.Value="Approved",

'Selection ComboBox'.Selected.Value='Column1'),'Activity',If(SortDescending1,SortOrder.Descending,SortOrder.Ascending))

This seems to return a list of Activities approved for any of the combobox selections, I need it to only return Activities approved for ALL of the combobox selections. I've tried to insert for all, but I'm not sure if that is correct and I cannot get it to work anyway), and I don't think concatenating is the solution either. 

I then tried: 

ForAll('Selection ComboBox'.SelectedItems, Filter(

'List B', Status.Value="Approved",

'Selection ComboBox'

.Selected.Value='Column1')) but this appeared to return different items entirely. 

 

This is a large data set so I'm also concerned about delegation. 

[Giraldoj]

Hi @ayattaw 

To filter your gallery based on multiple selections from a ComboBox, ensuring all selected criteria are met, you can use a combination of the ForAll and CountIf functions. This approach ensures that the gallery only shows items where all ComboBox selections return true.

First, I changed the items property of your ComboBox to ensure it's filtering correctly from 'List A', excluding archived items and optionally showing all options. The new Items property for the ComboBox is:

Distinct(Filter('List A', 'Archived?'.Value ="No" && ('Dropdown1'.Selected.Value="All Options" || 'Dropdown1'.Selected in ColumnX)), Title)

This change ensures that your ComboBox only displays active, relevant items from 'List A'.

Now, for your gallery, use the following formula in the Items property to filter 'List B' based on the selected items in the ComboBox:

Filter(
    'List B',
    Status.Value = "Approved" &&
    ForAll(
        'Selection ComboBox'.SelectedItems,
        CountIf('List B', 'Column1'.Value = ThisRecord.Value) > 0
    )
)

This formula works as follows:

• It filters 'List B' to only include items where the status is "Approved".
• It uses ForAll to iterate over each selected item in the ComboBox.
• The CountIf function checks that for each selected item in the ComboBox, there is a corresponding item in 'List B' where 'Column1' matches. This ensures that the item meets all the selected criteria.

Now, if your dataset its large you are going to have some delegations problems with the filter, and forall functions, please consider to import your data into a collection first and then apply the forumula to your collection, that should fix any delegation issue.

If my response resolved your issue, please feel free to mark it as the solution by clicking accept it as a solution. 
This helps others find the answer more easily.

If you liked my solution, please give it a thumbs up. 
would be greatly appreciated. Thank you!

Connect with me: LinkedIn 

[ayattaw]

Thank you - I fixed the dropdown formula, but I'm getting errors on the items formula for the gallery. The CountIf function says "Invalid Argument. Expecting one of the following: Boolean, Number, Text, Decimal, Untyped Object". It also says "Delegation warning. The highlighted part of this formula might not work correctly on large data sets. The "CountIf" operation is not supported by this connector:.

Column1 is text not choice, and it would not let me apply the .value to it. ThisRecord.Value also errored, and the Intellisense only suggested [@ThisRecord].

I'm also confused about the need for the && instead of commas, doesn't the filter function automatically apply all of the conditions?

Last question, can you point me to a good resource for the trick for pulling my data into a collection for this purpose?

Thanks!