Difference between filtering data in CALCULATE with and without FILTER Expression

In Power BI, when you use the CALCULATE function to modify the filter context, there is a key difference between applying filters directly (without using the FILTER expression) versus applying filters through the FILTER function.

Here’s the difference:

1. Using CALCULATE without the FILTER expression (simple filters)

When you use CALCULATE without the FILTER expression, you apply simple filters directly on the columns or fields. These filters are typically expressed as conditions (e.g., Column = Value), and Power BI automatically applies them as part of the filter context.

CALCULATE(SUM(Sales[Amount]), Sales[Region] = "North")

2. Using CALCULATE with the FILTER expression (complex filters)

When you use CALCULATE with the FILTER function, you are applying a more complex or advanced filter that often involves multiple conditions or calculations, such as filtering on calculated columns or using logical expressions that can’t be expressed with simple filters.

CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Region] = "North" && Sales[Amount] > 100))