First do this Filter then do this

[bhanney2323]

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 

[RandyHayes]

@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.

[OceanA]

@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.

Status	Spot
Open	10
Open	20
Open	30
Closed	20

[RandyHayes]

@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)
    )
)

[bhanney2323]

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. 

[RandyHayes]

@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.

[bhanney2323]

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))

[RandyHayes · Accepted solution]

@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"
) 

[bhanney2323]

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