How to apply Multiple Filters to Power Apps Gallery: Dates, Ranges & More! 📅🎯

hello everyone Raza here in this video I will show you how to create multiple filters for galleries in powerapps we will focus on filtering based on dates date ranges set default values for filters and also combine it with other filters and all of these using simple formulas in powerfx so let's check it out in action [Music] I have a power app wherein I have a gallery that is connected to a very simple SharePoint list this list tracks sales discount requests I have the customer names the products for which they are requesting a discount the discount percent and the date address this discount is effective now effective date is a key metadata that I would like to provide filters on which allows the user to refine the results in the gallery based upon the date range selected currently the gallery is showcasing all the data if I was to pick a start date let's say the 1st of August the gallery will filter the data to show all the items where the effective date is from the 1st of August onwards if I also provide an end date to this let's say 15th of August the gallery gets filtered to list out the items where the effective date is in this date range if I reset Gallery showcases all the data from my list if I simply pick the end date 1st of August the gallery now showcases all the data where the effective date is first of August or anything less than that and in case I pick a start date that is greater than the end date I receive an error that start date cannot be greater than and date along with the date range filters I can also add other filters to my gallery for example the product so let's build this from scratch I am in the studio experience for the PowerApp I have a gallery the items property is connected to my SharePoint list that is connected in my power app that's my sales discount requests list I will be using the modern controls in powerapps to allow the user to filter the data in the gallery so I'll add a couple of text controls I have named the text as start date and end date and I will add a couple of date picker controls for the start date control I will rename it to DP start date and for the end date control I will rename it to DP and date following good naming standards now to apply that filter logic for the items property of the gallery I will use the filter function to filter my list I'll use the and function my date column in SharePoint is called effective date so effective date should be greater than or equal to the date picker control DP start date dot value I'll put a comma my second condition for and function will be effective date is less than or equal to DP end date dot value I'll close the and condition I'll close the filter condition I'll click format text now this formula assumes that we will always have values for start date and end date which may or may not be the case so to handle scenarios where they could be blank for the first condition I will use an or function and here I will use the function is blank to check the date picker DP start date has a value comma the actual condition itself and I'll close the or function and I will do something very similar for effective date less than or equal to the end date or is blank DP end date dot value comma that's the second expression now I'll click format text and that completes my formula let's preview the app let's pick a start date August 10th the gallery shows all the data from August 10th onwards now let's pick an end date of August 18th now the gallery will show me the data based on that date range now I would like to reset these filters and test other scenarios so for that I will insert a button text for the bottom I'll call it reset and on select of this button I will use the reset function to reset DP start date and reset DB and date those are my two date picker controls let's preview the app click reset the date picker controls are reset to blank the gallery shows all the data now let's pick an end date I'll pick the end date as July 25th so this should show me all the items that have an effective date until July 25th and if I click reset the gallery shows all the data we can also provide certain default values for example the date picker control for start date its value property is blank I will set this to today so now it's showing all the items where the effective date is today or greater and if I want to box this in a specific date range I can do that August 16th and this time if I click reset start date will always hold that default value which is today's date if you want to give a default value for the end date as well let's try that for the value property of this date picker for a date range of one month I will use the function date add today's date let's add one month and you can Define this unit in days months quarters in this scenario I'll pick one month if I preview notice it begins with the date range for one month I can change this let's say start from 4th of July up until the 3rd of September and if I click reset if you want scenarios where you want to force the date range to always be a month so end date add instead of today I will say DP start date dot value and if you don't want the user to change the end date it should purely be dependent upon the start date for the display mode property I will change the display mode of this control to disabled so we can see that I cannot change the end date but as I change the start date let's say August 16th this will give me a one month range and if the start date itself has a value empty the end date will also be empty so at that point it will show me all the items I've changed the display mode of the end date back to edit if you want to add other filters for your gallery and you wanted to work along with the date filters so let's add one for product Now product in my SharePoint list is a choice column when the following three choices so here I will use a drop down control I will rename this drop down control to DRP product for the items property I will use the function choices my SharePoint list Dot my column product which will list out all the choice values and for the modern drop down control there is a property called fields I will edit add field and pick value so now if I preview the app product will list out all the choices from my product Choice column so the user can select any product the reset button I will add one more reset action here to reset my product drop down so if the user clicks reset product gets reset to empty and to add this filter to my gallery in the and function once again I'll use or similar technique is blank DRP product Dot selected dot value comma my column and SharePoint is called Product it's a choice column so product.value is equal to DRP product dot selected dot value close the r function I'll click format text and that's my completed formula all my three filters will work in a logical and function let's try it out I'll pick product a so it gives me results only for product a I'll pick my start date as the 1st of August my end date was setting accordingly that was the default value and we can see the result in action I'll simply add a label control here the text property would be start date cannot be greater than end date and this control should be visible if the start date picker value is greater than the end date picker value so what I will do here is I'll add an and condition in this I'll add an or condition is blank DP start date dot value is blank DP and date dot value that completes the or condition put a comma close the and condition the result of or I will put it in in not condition meaning both of these dates need to have a value in order for this evaluation to take place so let's try it now I'll pick a start date 17th of August end date 31st filters my data I'll pick the end date as the first the start date is greater than the end date and I can see the label is giving me that specific message if you enjoyed this video then do like comment and subscribe to my YouTube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 59,606

Keywords: filter date range, powerapps filter, powerapps filter gallery, powerapps filter gallery by date, power apps, powerapps, filter, gallery, reza dorrani, how to, tutorial, powerapps gallery filter, powerapps multiple filters, powerapps filter data, filter function, date, date range, Data Filtering, Multiple Filters, Microsoft Power Apps, Dynamic Filters, Power Apps Tutorial, filter by date, filter in power apps gallery, how to apply multiple filters to powerapps gallery, multiple

Id: 1QflzfPyPe0

Length: 13min 49sec (829 seconds)

Published: Mon Aug 14 2023