cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Calculate with OR/AND/NOT in filter

It's unclear to me how I can use FILTER() in a CALCULATE() function, and apply an AND / OR / NOT logic.

Could you please refer to an existing help page or give examples here below? Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Calculate with OR/AND/NOT in filter

@jesuisbenjamin

I’ll give an example as below. Assuming we have a table like below.

If we want to get the total sales of China on July, we can use following formula.

```SalesFromChina_July =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER ( Table1, AND ( Table1[Country] = "China", MONTH ( Table1[Date] ) = 7 ) )
)```

If we want to get the total sales of China and USA on July and August, we can use following formula.

```SalesFromChina_OR_USA =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER ( Table1, OR ( Table1[Country] = "China", Table1[Country] = "USA" ) )
)```

If we want to get the total sales of India on July and August, we can use following formula.

```SalesFromIndia =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER (
Table1,
NOT (
OR ( Table1[Country] = "China", Table1[Country] = "USA" )
)
)
)```

Best Regards,

Herbert

4 REPLIES 4
Resolver II

## Re: Calculate with OR/AND/NOT in filter

To do an AND, you should create 2 filter statements, one for each.

=CALCULATE([measure],filter(),filter())

for an OR, do it like this

=CALCULATE([measure],filter(table,table[col1] = "X" || table[col2] = "Y"))

note this is not overly efficient, but it is how you do it. (working on 2 columns at once can be inefficient.

for NOT, you can use teh NOT() function around your tests

Hope that helps

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Microsoft

## Re: Calculate with OR/AND/NOT in filter

@jesuisbenjamin

I’ll give an example as below. Assuming we have a table like below.

If we want to get the total sales of China on July, we can use following formula.

```SalesFromChina_July =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER ( Table1, AND ( Table1[Country] = "China", MONTH ( Table1[Date] ) = 7 ) )
)```

If we want to get the total sales of China and USA on July and August, we can use following formula.

```SalesFromChina_OR_USA =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER ( Table1, OR ( Table1[Country] = "China", Table1[Country] = "USA" ) )
)```

If we want to get the total sales of India on July and August, we can use following formula.

```SalesFromIndia =
CALCULATE (
SUM ( Table1[Sales] ),
FILTER (
Table1,
NOT (
OR ( Table1[Country] = "China", Table1[Country] = "USA" )
)
)
)```

Best Regards,

Herbert

Helper I

## Re: Calculate with OR/AND/NOT in filter

Thanks. This is very clear.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors