I have a column in TableA that uses FILTER with multiple conditions that are OR-ed together. I am in Table A but need to filter Table B. It essentially goes like this:
A column in TableA =FILTER(TableB, "([Standard]=TableB[Item1][Standard] OR [Standard]=TableB[Item2][Standard] OR [Standard]=TableB[Item3][Standard] OR [Standard]=TableB[Item4][Standard]) AND [Name]=TableB[Name]")
This worked for some rows in TableB and not for others....not sure why. I'm also assuming that writing [Standard] is the same as TableA[Standard] as TableA is where I am writing the Filter function. However, I noticed that the Filter function returns a different result if I just write [Standard] vs TableA[Standard]. So, I do not think I am understanding the item referencing correctly...
To try to debug, I removed all of the [Standard]=TableB[ItemN][Standard] conditions and added them back into the filter one by one and noticed that when I add in certain conditions it actually removes results that are returned. For example, I will add in 3 of the conditions to the filter and that will return 5 results. But when I add the 4th condition back in I only get 3 results. Since these are OR-ed together, I do not understand why adding a condition would decrease the number of results returned because even if the condition is false, as long as at least one of the conditions is true then a result should be returned.
So, it seems that my ultimate problem is that having certain conditions gets rid of the results that are returned from the filter and I don't understand why. Perhaps I am misunderstanding the OR or AND operator?
I hope all of that makes sense. Thank you!