cancel
Showing results for
Did you mean:
Highlighted
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
Super User I

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

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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

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

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors