Filter must meet both conditions per row

(0) Share

Report

Posted on by HFG

524

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?

An example of what I have tried :

ClearCollect(

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

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

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (2)

Sort by

MVP-Phipps 3,521 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

TheRobRush 11,128 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
ClearCollect( colFruits Filter( 'VW.Fruits', And(Colour="Green", Or(Name="Apple", Name="Grapes"))=false ) )

Was this reply helpful? Yes No
HFG 524 on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like
a
(1)

Report
Copy link

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

Shown below is Test App I put together to display it

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

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
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)

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
HFG 524 on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Verified answer

HFG 524 on at

Like
a
(1)

Report
Copy link

Link copied!

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

Thanks for your help!

Was this reply helpful? Yes No