Multi filters

[BORUCH]

HI
I HAVE THIS FORMULA

Sales Flag:=IF(NOT(ISEMPTY(CALCULATETABLE('sales','sales'[Qty Ord] >0))),"Had Sales","Had No Sales")

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)
HAD SALES
HAD NO SALES

 

[Matt Allington]

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]

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]

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]

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”

 

[BORUCH]

Any updates to my question?

 

[Matt Allington]

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))

 

[BORUCH]

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))

Thanks for your time

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

 

[BORUCH]

any updates to my request

 

[Matt Allington]

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