Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Count orders that does not include x (Filter granularity)

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

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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]

     

     

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    9 years ago

    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