cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AFernandez
Level: Powered On

Filtering a Gallery by Multiple DropDowns.

Hi,  

 

I´m working on an app that pulls data from a sharepoint list. 

 

In the Main Screen, I Have a Gallery that is being filtered by 2 DropDowns.  One filters the gallery by Department (Claims, IT, HR, Security...), the other by Levels of the items (Very High, High, Medium...).  I listed the items on each dropbox manualy, I did not pull them from the SharePoint list as the options are fixed and will not change. 

 

I also have a label that shows the count of the rows in the gallery according to the filters. Right now, I can filter the gallery by Department and the label shows the number of items by department or I can filter it by Level and it does the same. 

 

This works, filtering the gallery by each dropdown independently, so that the label shows the number of items in the gallery filtered by each dropdown (by department or by Level), but I also want to be able to filter by the 2 dropdonws at the same time, so that users can see the number of departments with a certain level. 

 

Here is the code I´m using right now:

 

SortByColumns(

If(Dropdown3_Department.Selected.Value <> "All",

 Filter('Data', Department = Dropdown3_Department.Selected.Value),

If(Dropdown4_Level.Value <> "All",

Filter(Data', Level = Dropdown4_Level.Selected.Value && "IS" in IdCode),

Filter('Data',"IS" in IdCode && Level <> "N/A"))),

"Level",Ascending)

 

I want to be able to dynamically show:

1. Number of items in the gallery by Department (Works right now).

2. Number of items in the gallery by Level (Works right now)..

3. Number of items by Department by Level (exm: How many Items by IT, whith Very High level).

 

Really, appreciate any help, thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filtering a Gallery by Multiple DropDowns.

If you have a small SP list (under 2k items) then this formula might work for you. If uses an If() in the Filter()  and the If() isn't delegable in SharePoint, so you will get a delegation warning.

 

SortByColumns(
	Filter('Data',
		If(Dropdown3_Department.Selected.Value <> "All",
			Department = Dropdown3_Department.Selected.Value,
			true
		),
		IF(Dropdown4_Level.Value <> "All",
			Level = Dropdown4_Level.Selected.Value && "IS" in IdCode,
			"IS" in IdCode && Level <> "N/A"
		)
	),
	"Level",
	Ascending
)


--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Filtering a Gallery by Multiple DropDowns.

If you have a small SP list (under 2k items) then this formula might work for you. If uses an If() in the Filter()  and the If() isn't delegable in SharePoint, so you will get a delegation warning.

 

SortByColumns(
	Filter('Data',
		If(Dropdown3_Department.Selected.Value <> "All",
			Department = Dropdown3_Department.Selected.Value,
			true
		),
		IF(Dropdown4_Level.Value <> "All",
			Level = Dropdown4_Level.Selected.Value && "IS" in IdCode,
			"IS" in IdCode && Level <> "N/A"
		)
	),
	"Level",
	Ascending
)


--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

AFernandez
Level: Powered On

Re: Filtering a Gallery by Multiple DropDowns.

Thank you so much!  I had to add the && "IS" in Department && Level <> "N/A" to the end of the first IF statement for it to work as I wanted, but after that it worked perfectly.

 

Could you explain a little the logic behind the formula? I still dont quite get how it works.  Thanks again! 

Super User
Super User

Re: Filtering a Gallery by Multiple DropDowns.

A Filter() can have multiple conditions separate by a comma and they work as an "And" statement. Then I wrapped some If() around each condition so each condition would be slightly different based on what was selected. The "true" Boolean just means that record meets the condition.



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (5,309)