Filter gallery using two combo boxes

[MHSPDev]

Hey all,

I'm currently building a PowerApps frontend for an internal Risk Register, utilising a SharePoint list as the data source.

I have two combo boxes I'd like to use for filtering, the first combo box is the Risk Owner (Person field), the second is the Risk Status (choice field).

My current formula for the gallery is:

Filter(
      'Risk Register',
      Or(
              IsBlank(ComboBox1.SelectedItems) && IsBlank(ComboBox2.SelectedItems),
              IsEmpty(ComboBox1.SelectedItems) && IsEmpty(ComboBox2.SelectedItems),
              RiskOwner.DisplayName in ComboBox1.SelectedItems.DisplayName && RiskStatus.Value in ComboBox2.SelectedItems.Value
         )
)

Now this half works, if I don't select either filter I can view all of the risks, great.

If I use both of the filters I can view the risks I've filtered by, great.

If I use just one of either filter, the list goes blank and I cannot view any risks until I either remove the filter or use both filters.

I want to be able to filter by either or both combo boxes, not both of them or none of them.

I've tried googling this problem for a couple of hours now and have not been able to make any progress, thanks in advance!

[mmbr1606]

hey @MHSPDev 

please try this 

Filter(
    'Risk Register',
    (IsBlank(ComboBox1.SelectedItems) || RiskOwner.DisplayName in ComboBox1.SelectedItems.DisplayName) &&
    (IsBlank(ComboBox2.SelectedItems) || RiskStatus.Value in ComboBox2.SelectedItems.Value)
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[SpongYe · Accepted solution]

Try this:

Filter(
    'Risk Register',
    (IsBlank(ComboBox1.SelectedItems) || IsEmpty(ComboBox1.SelectedItems) || 
    RiskOwner.DisplayName in ComboBox1.SelectedItems.DisplayName) && 
    (IsBlank(ComboBox2.SelectedItems) || IsEmpty(ComboBox2.SelectedItems) || 
    RiskStatus.Value in ComboBox2.SelectedItems.Value)
)

If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻

[MHSPDev]

Hey there,

Thanks for the fast response, unfortunately this makes it work only if both filters are selected, so a slight step back as we need all records to display if no filters are working and it still doesn't work if only one of the filters are selected.

[MHSPDev]

Wow, that was fast.

All working now and I have a better understanding of where I went wrong.

Thanks!

[mmbr1606]

hey

you ca also try this

Filter(
    'Risk Register',
    (ComboBox1.SelectedItems.Count = 0 || RiskOwner.DisplayName in ComboBox1.SelectedItems.DisplayName) &&
    (ComboBox2.SelectedItems.Count = 0 || RiskStatus.Value in ComboBox2.SelectedItems.Value)
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings