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

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors