cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HFG
Advocate III
Advocate III

Filter must meet both conditions per row

Hi all, 

 

Something I feel I should be able to do is proving tricky so I'm turning to you all for help. 

 

I have a set of data something like below. I want to keep the rows in yellow. I want to exclude Apples and Grapes if they are Green, but keep all the other green rows. So I need that the condition to be not equal to Apple or Grapes and colour not equal to Green. Everything I have tried filters out the other rows because of the colour being green. How can I get it to look at both conditions for each row? 
HFG_0-1656430085865.png
An example of what I have tried :

 

ClearCollect(

            colFruits
                Filter(
                   'VW.Fruits'
                        , (Name <> "Apple" && Colour<> "Red")
                       &&

                      (Name<> "Grapes" && Colour<> "Red")
)

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
TheRobRush
Super User
Super User

ClearCollect(

            colFruits
                Filter(
                   'VW.Fruits', 
                   And(Colour="Green", Or(Name="Apple", Name="Grapes"))=false
                        )
                )
_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

View solution in original post

HFG
Advocate III
Advocate III

@TheRobRush if I first collect the view into a collection and then filter it using your code it works. Weird! 

 

Thanks for your help!

View solution in original post

8 REPLIES 8
phipps0218
Memorable Member
Memorable Member

Can you try this and then show me what appears in the collection?

 

You want the collection to include everything in yellow so you should have:

 

ClearCollect(

            colFruits
                Filter(
                   'VW.Fruits',

Name <> "Apple" && Colour<> "Green" &&

                      Name<> "Grapes" && Colour<> "Green"
)

 

That will provide you a collection of all those in yellow.

 

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful as can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
Subscribe to my YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg

TheRobRush
Super User
Super User

ClearCollect(

            colFruits
                Filter(
                   'VW.Fruits', 
                   And(Colour="Green", Or(Name="Apple", Name="Grapes"))=false
                        )
                )
_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Hi @phipps0218 and @TheRobRush 

 

Sadly, neither of those solutions have worked. In the first case everything was filtered out because they all are either Apples/Grapes or Green. 

I was hopeful with the second suggestion, but it also filtered out everything. I'll try variations of that though and see if something works. 

Let me know if you have any other ideas.

 

Thanks!

TheRobRush
Super User
Super User

I built a collection that is the exact one pictured in your example.

 

Shown below is Test App I put together to display it

 

TheRobRush_0-1656439485159.png

On left is the full collection, and I gathered it liek this

in onselect of the button below it

 

ClearCollect(SampleTable,
         {Name:"Apple", Colour:"Red"},
         {Name:"Apple", Colour:"Green"},
         {Name:"Grapes", Colour:"Red"},
         {Name:"Grapes", Colour:"Green"},
         {Name:"Pear", Colour:"Green"},
         {Name:"Courgette", Colour:"Green"},
         {Name:"Cucumber", Colour:"Green"})

 

on the right is the narrowed collection, which was collected like so

OnSelect

 

ClearCollect(FruitTable,
            Filter(SampleTable, And(Colour="Green", Or(Name="Apple", Name="Grapes"))=false))

 

 

If that is not working for you same as in my screenshot, there's something wrong with how it's been written when plugged in on your side. (Unless results bring in more than 2k items, or 500 if you haven't chanegd default items count)

 

I would recommend making another screen in your app and recreating the above so that you can see it in action and get a better understanding  of how it is working for when you apply it to your own data

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
TheRobRush
Super User
Super User

Can also skip the collect and make it the source of the gallery like so

Filter(SampleTable, And(Colour="Green", Or(Name="Apple", Name="Grapes"))=false)
_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
TheRobRush
Super User
Super User

We can also keep full list in a gallery and put a yellow  box behind the lot with a visible set to

And(ThisItem.Colour="Green", Or(ThisItem.Name="Apple", ThisItem.Name="Grapes"))=false

then we get

TheRobRush_0-1656440016362.png

 

 

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Hi @TheRobRush 

Thanks for taking the time to look into this. I copied your code and indeed it works perfectly, but still not working on my data. As you probably guessed, my table is not actually a table about fruits, I used it as an example. My table is actually a SQL view where there is a text column (Name) pulled from a table and a text column (Type) created in the view. The result is being used in a combo box.

At worst I can filter out the rows I don't want using the ID but it's risky if my data changes at some point. 

I'll keep trying!

HFG
Advocate III
Advocate III

@TheRobRush if I first collect the view into a collection and then filter it using your code it works. Weird! 

 

Thanks for your help!

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (2,510)