Power Apps How To Filter with Multiple Dropdowns and Blank Selections to Unfilter

filtering data in powerapps i'm not gonna sit here and lie to you it should be so much easier than it is when i first started working on powerapps i was like okay somewhere there is a button i just click to tell it yeah i want to filter by this column i want to filter by that column you can't do that but it is very easy it's very simple and there is so much that you can do with it so this is going to be a quick video run through of a whole bunch of different type of scenarios for filtering uh filtering with drop downs filtering with drop downs that you can have a blank selection to unfilter the data so stick with me i'll run through kind of from beginner to advanced how to filter your data thank you for joining and welcome back to power to the people filtering in power ups can be easy or it can be hard it all depends on how crazy you want to get and how advanced you want to get with your filtering like literally you can take it to pretty crazy levels really kind of all depends on how advanced you want your app and also kind of how annoying your users are no offense like they'll reach out to you all the time like hey i need to filter by this can you do this so the the more of that you get the the better you get at filtering your data so before we can get into the advanced stuff we kind of need to first understand at a basic level how to filter data so i've got this quick app set up with a data source it's just a list of companies from wikipedia like the largest companies in the us by revenue or something like that so first let's just go to the data with no filters so we can kind of see how it's laid out we've got the company the industry revenue employees and where their headquarters are so what we see here as you probably know by this point the items in this data table aka the data source is just the name of the data source so if we want to filter this data let's say we want to filter it by the type of industry the clients are in let's go and see how that looks with one filter so what i have here is a drop down so a standard drop down when you add it the the default for like the ability to have an empty selection i believe is false so there's always going to be something selected in this drop down and the drop down as you can see if we go to items for the drop down it's basically the it's tied directly to the data source to this column and it's looking at the distinct industries so it's not going to list the same industry twice on here it's just the distinct list of the industries in this data set and it's basically sorting those by um in alphabetical order so if we look at the data table we see there's a filter formula and then customer data and then some other stuff so really quickly let me jump over to this other screen i have just to show like what the filter formula is so basically the filter formula is filter parentheses your data source and then the logical statement um so what i mean by logical statement that that basically is true or false anything that's logical that means true or false so let's look at um with basically one filter which what we just had in the app so we have example for one filter is we have the filter parentheses and then customer data which is the name of our data source and the logical statement is the drop down basically dropdown.selected.result so that's like a text value so the dropdown dot selected so whichever item is selected in the drop down the text for that equals you know anything that equals this in the industry column so that's the logical statement so if we flip back over to powerapps we see um filter customer data drop down one equals industry so it's showing everything in the data where arrows like the industry equals aerospace and defense so if we switch that to advertising we get the list of advertising companies that are on this list we switch to auto retailer we get these so what you'll see with this drop down it's always going to be filtering the data you'll never be able to see the data as like a whole list of items it's always going to be filtered in some way so that's not what everyone wants you want to usually start with uh seeing all of the data and then you want to filter it down pare it down from there to get exactly what you're looking at so um how you do that with a drop down is there's a property within the drop down if we go to advanced and we go to more options here allow empty selection is set to false so actually there's a couple ways to do that so obviously my way it would be to set it to true allowing you to have an empty selection another way would be to add a blank or to add an all somehow the problem with that is if you want to tie your drop down directly to your data source there's not going to be any industries in this list called all or called blank so you'll never be able to add it unless you start a collection in the app and then add in the all or add in the blank it's like that's not the best way to do it i've seen so many videos of how to filter stuff and once we get towards the end i i've never seen a video of it doing it this way maybe i came up with a new way to filter i highly doubt that but who knows maybe i did so the best way that i think so let's go over to one filter with a blank so this is going to incorporate another formula so it's going to incorporate an if statement so we'll go back here real quick and i want to just show the if formula so if statements are basically uh if parentheses and then the logical statement and then the true value and then the false value so if this then this and if not then this if this then this and if not then this so if you flip back over here to our app we see um and if anyone's more advanced in powerapps you might be saying nate like if statements in a filter formula is not delegable you're going to get issues if your data gets over a certain number that's true if the if statement is in the logical part of the filter formula whereas if the if statement comes before the filtering or even if the if statement is part of the data source which we'll get to later as you have more drop downs and more options that it is delicable if your if statement is before your filtering or if your if statement is part of your data source is just as long as it's not a part of the logical statement of a filter it's completely delegable you're gonna have no issues whatsoever which is really cool so we see here if is blank drop down one dot selected dot result then show customer data so it's saying if this drop down is blank show all of customer data unfiltered if it is not blank then filter customer data by the industry equals whatever you have selected here right so right here it's blank and we see everything a whole mix of industries whereas if we select one we go to automotive and we see okay now it's filtered by automotive with this drop down now we can select automotive again to basically unselect it and it's going to go back to all of our data so again that property for the drop down is right here in advanced and we go to allow empty selection it's now set to true it's saying you can have an empty selection in this so the default is blank and once you select something again if you want to unselect it you just click the item that's selected and it goes back to all your data unfiltered here's where it starts to get a little trickier but again i think the way that i've come up with this is pretty easy and i'll show you how i got there two filters is basically the same thing as one filter but instead of just showing um here i'll show you instead of having one um logical statement here we have two and they're separated by a comma very simple you don't have to do like multiple filter formulas you don't have to do an and statement in here it's just it knows that if you add a comma and then add another logical statement that you want to filter by both of these these things so now we have two drop-downs and this drop-down is connected to the state column of this data source so now we've got all of our states sorted alphabetically and we see there's no companies that are advertising in alabama so let's go advertising there's probably some in new york so we go to new york and we see we've got two here if we want still new york companies let's say we just want new york companies we can't do that right because this these drop downs are you have to have something selected which kind of stinks so now what we're going to do we're going to get a little more advanced and we're going to go to two filters with the ability to have a blank selection so just like before we're going to set both of these drop downs to allow empty selections and that's going to show all of our data but it it starts to get a little complicated so this is the way that i've come up with and i think it's once you like understand it it's very simple but it looks complicated so first things first let's start with our data right again i suck at typing i know that so customer data so this is all of our customer data but now we want to filter it by let's say industry first so let's see what this item is called it's drop down one so go back so we'll say if is blank drop down one so basically if if the drop down is blank then show customer data all of it and if it's not blank then filter customer data by drop down one again this is the logical statement we're saying if drop down one the selected item we want it to equal the industry okay and we'll close out both sets of parentheses and now we've got that one filter with blank just like on this last screen that we're at so now think about it this way this is our data source and it's showing the data so it works right so now what we want to do is copy this whole string right because this is now our data source and why that matters so let's copy it over into a anywhere really and why that matters for this being the data source is because now we're going to add another if statement outside of it it's like a nested it's an if statement within an if statement so we're going to say if is blank and we're going to go see what this one's called it's called state filter sorry for the non-consistent naming nomenclature so if state filter dot selected so if this is blank right then the true value is show this show that thing from before with the if statement in there so it's like i don't know cascading drop downs with blanks but the good thing is we don't have to like remember anything we just copy and paste so it you start with the customer data you start with the data source itself and you kind of build the formula out from there you don't you don't just go into it knowing like i'm going to say if this is blank then the if this is blank then this and then if not like it it's impossible who could do that i mean probably smarter people but not me um so you want to start with the name of the data source and kind of just build the formula out from there so now we're saying okay sorry to get off track there we're saying if this is blank then show the formula we had for this right the filter formula if it's not blank then filter and remember this thing isn't our data source so copy this because the filter formula is asking for the data source so copy this so filter all of that by state filter again that's the name of our drop down equals state column close that out and now what you can do is you can filter by let's see hmm auto parts in washington there's none well who is what industries are in washington so we deselect that and we see washington is filtered here and here's all the industries on the flip side of that if we want to see auto parts and hm oh there's no big auto parts companies in washington well where are they so we see you know unfilter that georgia illinois illinois so then the last thing again so this is an easy one we're not going to do any if blanks but since we now understand like this can be a data source so we'll go ahead and say we've got a slider here too let's filter by the number of employees employees starting with zero so we can wrap this whole thing in a filter formula with this as the data source so let's go ahead and say filter all of this by and the slider it's called slider1 so we'll say number of employees which is employees that's the name of the column i think yeah employees so we'll say the number of employees is greater than or equal to slider one dot value so now if we want all of these blank we can set all these to blank if we just want to filter by the number of employees we can slide this to filter anything you know more than the the slider is going to show up in our value or data here so let's say we want aerospace and defense companies with more than a hundred thousand employees we get lockheed martin so um that's pretty much it in a nutshell like i said my biggest tip would be to start with your data source filter it once filter it twice just keep expanding it out from there and then your if statements don't have them be part of the logical statement of a filter just have them be either part of the data source or come before the data source and there will be no delegation issues whatsoever thanks again for tuning in again it's nate with power to the people i'm trying to just see where this channel goes see if people like it so if you did come across this video and you're not subscribed just um go ahead and hit the subscribe button got a lot more interesting content coming your way um some other exciting things i've got a website coming out that's going to contain a lot of this information as well so you don't have to just watch my stupid face struggle with video production and try to follow along you'll be able to just go to the site and copy and paste any formulas you need also i recently just set up a github repository where any app that i work on or any you know video that i make that has these kind of fake apps in there i'll go ahead and upload those to github so that you can download them and mess around play with the formulas so more to come on that but stay tuned and thanks again for watching it's more power to you and power to the people

Channel: Power to the People

Views: 2,239

Rating: 5 out of 5

Keywords: power apps, powerapps, power apps tutorial, powerapps tutorial, power apps how to, powerapps how to, powerapps filter, filter powerapps, power apps filter, filtering, how to filter, filter power apps, delegable filter

Id: 0bRR-Zq407c

Length: 18min 23sec (1103 seconds)

Published: Sun Jan 10 2021

Reddit Comments

[u/tyfen_]

It’s just nested if/filter statements.