Count orders that does not include x (Filter granularity)

[Anonymous]

Hi,

I have a sales tables, with 1 line for every item in an order.

My goal is to count the amount of orders that has a revenue below 319,2 and were the order does not include an item from the category "FOOD".

Currently I'm using the DAX function below, which work at a line level, but I still get a count of 1, if an order includes a "FOOD" item, and other items as well. Orders that only include "FOOD" are filtered out by the function.

How do I convert to formula and adjust the granularity , so it excludes orders as soon as an item from the "FOOD" category appears on the order ID?

Thanks.

Orders below 319,2, NON-FOOD = 
CALCULATE(
	COUNTROWS(VALUES(Sale[OrderID]));
	  FILTER(VALUES(Salg[OrderID]);Sale[Sales Revenue] < 	319,2);
	  FILTER(Product;Product[MainGroup] <> "FOOD")
)

[dedelman_clng]

When you're in CALCULATE, everything *after* the value to be calculated (in your case COUNTROWS(VALUES(Sale[OrderID])) ) contains an implicit FILTER.  So you should be able to just put the conditions you are looking for in the CALCULATE statement:

Orders below 319,2, NON-FOOD = 
CALCULATE(
	COUNTROWS(VALUES(Sale[OrderID]));
	  Sale[Sales Revenue] < 319,2;
	  Product[MainGroup] <> "FOOD"
)

Hope this helps

David

[Anonymous]

Made a workaround by creating two different measures, and then calculating the difference between the two, to get my desired result, but surely there must be another way?

Orders below 319,2 = CALCULATE(
		COUNTROWS(VALUES(Sales[OrderID.]));
		FILTER(VALUES(Sales[OrderID]);Sale[Sales Revenue] < 319,2)
)
																	

Orders below 319,2 that includes FOOD:

Orders below 319,2 incl. FOOD = CALCULATE(
		COUNTROWS(VALUES(Sales[OrderID]));
			FILTER(VALUES(Sales[OrderID]);Sales[Sales Revenue] < 319,2);
			FILTER(Product;Product[MainGroup] = "FOOD")
)

Orders that doesn't include food:

Orders below 319, ex FOOD = [Orders below 319,2] - [Orders below 319,2 incl. FOOD]