cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhanney2323
Resolver I
Resolver I

First do this Filter then do this

Hi, is there a way to have a filter setup so that it takes the first couple of filter results and then you further filter it by another set of drop downs? Below I have two drop downs and they filter fine and then a person can select an item from the gallery and get more granular info on that item; however, is it possible to further filter by things like outer diameter and inner diameter after of course the parent and sub category drop down filters are applied? I know I can add things to the initial filter like if I made an outer diameter drop down and added a "&& outer_diam_dd.selected.result = outer diameter" to the data table or gallery, but I really want to be able for it to first just do those two first filters and then take that pre-filtered data and filter again. Let me know if that makes sense, I know its confusing. 

 

Btw this is all pulling from the same SQL data source 

bhanney2323_0-1598301974002.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

@bhanney2323 

Actually, I'm not sure where you're seeing that my formula would just return blank records.  The only test of Blank had to do with your dropdown boxes.  I was assuming you did not have a "no selection" item in your dropdown and was suggesting to set the allow blank to true so that you could have one.  But, if you already have the " - " in your items, then you can change the formula to this:

Search(
    Filter(
        '[dbo].[970_item_format_item_mast]',
        (Parent_cat_dd.Selected.Result = " - " || parent_category = Parent_cat_dd.Selected.Result) &&
        (Sub_Cat_dd.Selected.Result = " - " || sub_category = Sub_Cat_dd.Selected.Result)
    ),
    'Itemid/desc Searchbox'.Text, "item_id", "item_desc"
) 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

8 REPLIES 8
RandyHayes
Super User
Super User

@bhanney2323 

Yes, you can nest filters as much as you want.

 

Example:

Filter(
    Filter(
        Filter(myDataSource, Status="Open"),
        StartDate > DateAdd(Now(), -2, Months)
    ),
    Spot = 10
)
    

This will first filter by status of Open. Then filter those results for startdate within the past 2 months.  And finally filter those results to records where Spot is 10.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes How would you use the value of the previous filter in the next level down, and add this to a gallery? 

 

For example, I want to Spots which have rows with only open status. So if Spot 20 has a row with status as open, but has another row with status that is closed, then Spot 20 won't be included. 

Only the red rows should be picked up.

 

StatusSpot
Open10
Open20
Open30
Closed20

 

@OceanA 

For that you could double filter like this:

With({data:
    Table(
        {Status:"Open", Spot:10},
        {Status:"Open", Spot:20},
        {Status:"Open", Spot:30},
        {Status:"Closed", Spot:20}
    )},
    Filter(
        Filter(data, Status="Open"),
        !(Spot in Filter(data, Status="Closed").Spot)
    )
)

Or you could do it in one filter:

With({data:
    Table(
        {Status:"Open", Spot:10},
        {Status:"Open", Spot:20},
        {Status:"Open", Spot:30},
        {Status:"Closed", Spot:20}
    )},
    Filter(data,
        Status="Open" && 
        !(Spot in Filter(data, Status="Closed").Spot)
    )
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

So my table is much larger than that, how would I do it so it takes the first filter and then shows those results on a data table or gallery and then takes the second filter if it is chosen and then after the second filter is chosen, both filters are applied. 

 

The problem is right now is that when I choose the first filter from the drop down, I need it to show that filter and that filter alone without also applying the second with the first filter. I need them to be independent of one another if the other has not been selected. 

@bhanney2323 

Yes, so first off, how do you consider "if it is chosen"?  From your original picture, your dropdowns both have values - and thus would be considered "Chosen".  If you want to have them not be chosen, then you can set the AllowBlankValues to true on the dropdown and use that for your advantage.

 

And then your filter would look something like this.

Filter(
    yourDataSource,
    (IsBlank(ddCatagory.Selected.Value) || ddCategory.Selected.Value = yourCategoryField) &&
    (IsBlank(ddSubCat.Selected.Value) || ddSubCat.Selected.Value = yourSubCategoryField) &&
    (IsBlank(outer_diam_dd.Selected.Result) || outer_diam_dd.Selected.Result=outerDiameterField)
)

 

This would give you results based on nothing being chosen, then by any dropdown being selected individually or together.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

So right now the default for the drop downs is " - ". I set them up as collections and what I want is for the filter to show all if " - " is selected on either but if one of them is selected it will filter that drop down and then if the other is selected along with the first filter it will apply both filters. In your scenario it won't work because it will just filter the data source for blank records. 

 

This is the current filters I have on my gallery

 

If(Parent_cat_dd.Selected.Result <> " - " && Sub_Cat_dd.Selected.Result <> " - ",
Search(Filter('[dbo].[970_item_format_item_mast]', parent_category = Parent_cat_dd.Selected.Result &&
sub_category = Sub_Cat_dd.Selected.Result),
'Itemid/desc Searchbox'.Text,"item_id","item_desc"),Filter('[dbo].[970_item_format_item_mast]', parent_category = Parent_cat_dd.Selected.Result))

@bhanney2323 

Actually, I'm not sure where you're seeing that my formula would just return blank records.  The only test of Blank had to do with your dropdown boxes.  I was assuming you did not have a "no selection" item in your dropdown and was suggesting to set the allow blank to true so that you could have one.  But, if you already have the " - " in your items, then you can change the formula to this:

Search(
    Filter(
        '[dbo].[970_item_format_item_mast]',
        (Parent_cat_dd.Selected.Result = " - " || parent_category = Parent_cat_dd.Selected.Result) &&
        (Sub_Cat_dd.Selected.Result = " - " || sub_category = Sub_Cat_dd.Selected.Result)
    ),
    'Itemid/desc Searchbox'.Text, "item_id", "item_desc"
) 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thank you so much, this worked and I'm sorry that was my bad, I just assumed that it would search for blank fields. Thanks again, you made my day a whole lot better

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (5,906)