Multi filters | MrExcel Message Board
Thread starter BORUCH
Sales Flag:=IF(NOT(ISEMPTY(CALCULATETABLE('sales','sales'[Qty Ord] >0))),"Had Sales","Had No Sales")
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
HOW CAN I HAVE THIS AS A SLICER (FILTER)
HAD NO SALES
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)
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.
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”
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.
any updates to my request
I don't have any suggestions about doing it the way you want to do it.
Multi filters | MrExcel Message Board
Thread starter BORUCH
Sales Flag:=IF(NOT(ISEMPTY(CALCULATETABLE('sales','sales'[Qty Ord] >0))),"Had Sales","Had No Sales")
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
HOW CAN I HAVE THIS AS A SLICER (FILTER)
HAD NO SALES
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)
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.
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”
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.
any updates to my request
I don't have any suggestions about doing it the way you want to do it.