cancel
Showing results for
Search instead for
Did you mean:
Anonymous
Not applicable

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

2 REPLIES 2
Highlighted
Anonymous
Not applicable

## Re: Count orders that does not include x (Filter granularity)

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]`

Highlighted
Memorable Member

## Re: Count orders that does not include x (Filter granularity)

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

## Helpful resources

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors
Top Kudoed Authors