I have a dataset where I need to filter out lines of data that contains 0 in two fields. The current filter I have is:
[AP Amount] != 0 and [Expense Amount] != 0
Unfortunately this filter removes all lines of data that have a zero. I'm looking to keep lines of data that may have a 0 in one field and a value in the other. Can anyone assist?
Solved! Go to Solution.
Hi @trailfarley,
I think you need to use this formula:
[AP Amount] = 0 and [Expense Amount] = 0
And connect the data to the false anchor of the filter tool.
Don't ask me why you need to do it like that but trust me it will work 😛
sorry I may not be following what to do to the false anchor. Could you elaborate?
Alternatively to @Emil_Kos's solution, change the AND in your existing filter to be OR. It's a bit counterintuitive but I think it's a product of using the not equal to operator.
Another way to set it up is to use
!([AP Amount]=0 and [Expense Amount]=0)
which takes @Emil_Kos logic and negates it to send it out the true anchor. The two statements
!([AP Amount]=0 and [Expense Amount]=0)
[AP Amount]!=0 OR [Expense Amount]!=0
are logically equivalent
Dan
Hi @danilang,
Very elegant and very simple:
!([AP Amount]=0 and [Expense Amount]=0)
I will keep that in mind in the future as I always build it another way around.