Filtering on Multiple Fields for Zeros

[trailfarley]

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?

[Emil_Kos]

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 😛 

[trailfarley]

sorry I may not be following what to do to the false anchor. Could you elaborate?

[Luke_C · Accepted solution]

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.

[Emil_Kos]

Hi @trailfarley,

I have prepared a workflow for you:

[Emil_Kos]

Hi @Luke_C,

Actually, your way is better as this makes more sense to connect to true anchor. 

[danilang]

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

[Emil_Kos]

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.