Forum Discussion

jesuisbenjamin's avatar
jesuisbenjamin
Icon for Advocate I rankAdvocate I
10 years ago
Solved

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.
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    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