# Multi filters

#### BORUCH

##### Active Member
HI
I HAVE THIS FORMULA

Two questions

#1
how can i have this formula also do a filter for [qty ord] is less the ZERO
I want it to tell me only if its less then zero or greater the zero not equal zero

#2
HOW CAN I HAVE THIS AS A SLICER (FILTER)

#### Matt Allington

##### MrExcel MVP
Regarding 1, why do you have this formula? What is its purpose? The reason I ask is that Power Pivot is different to Excel, and you don't need to replicate Excel concepts - there can be different/better approaches.

#### BORUCH

##### Active Member
Regarding 1, why do you have this formula? What is its purpose? The reason I ask is that Power Pivot is different to Excel, and you don't need to replicate Excel concepts - there can be different/better approaches.
This formula tells me if an item was sold for that customer, or for that month, or whatever, it looks at my detail invoice history table and tells me if that item is there (IF IT WAS SOLD)

#### Matt Allington

##### MrExcel MVP
So why do you need it? If you put items in a pivot table for a month, if it sold you will see it, it if didn't sell you won't see it. If you want to see products that didn't sell, you can write a measure for that too.

#### BORUCH

##### Active Member
ok let me explain

I have my products table connected to my invoice history detail table, so I can drag my item # from my products table, and then drag my sales flag to the values section, and I can filter by my customer # in my detail table to see if that customer bought that product this month etc.

If you have a better formula please let me know, this is a very simple formula I just want to add another filter, and be able to filter by “has sales”, and “has no sales”

#### Matt Allington

##### MrExcel MVP
Haven't confirmed, but I assume your formula is a calc column. The problem is that it is a static result based on the latest data. If you want to see customers that didn't purchase last month, or time series trend you can't do it with this approach?

try this as a measure for products that sell

=calculate(countrows(products),InvoiceHistoryTableName)
and products that don't sell

=calculate(countrows(products),filter(products,[total Sales measure]=0))

Last edited:

#### BORUCH

##### Active Member
Haven't confirmed, but I assume your formula is a calc column. The problem is that it is a static result based on the latest data. If you want to see customers that didn't purchase last month, or time series trend you can't do it with this approach?

try this as a measure for products that sell

=calculate(countrows(products),InvoiceHistoryTableName)
and products that don't sell

=calculate(countrows(products),filter(products,[total Sales measure]=0))

I would like to keep my original formula, which works best for me!, just to have the ability to have two filters, and have the ability to have a slicer, with two options, have sales, and have no sales.
this is the easiest way for me, any suggestions maybe a disconnected table is appreciated.

Thanks

#### Matt Allington

##### MrExcel MVP
I don't have any suggestions about doing it the way you want to do it.