<!-- https://learnsql.cpetoday.com/analyze/calculate -->
# CALCULATE and filter context

CALCULATE is the most important function in DAX: `CALCULATE(<expression>, <filter1>, <filter2>, ...)` evaluates the expression in a filter context that you modify. Almost every non-trivial measure is CALCULATE in some form.

## How CALCULATE works
1. The incoming filter context (slicers, rows, columns) is copied.
2. Any row context is converted to filters (context transition).
3. Each filter argument is applied: it either replaces, removes or adds to the filters on the columns it names.
4. The expression is evaluated in the new context.
Filter arguments are evaluated in the ORIGINAL context and then applied together as an AND, never one after another.

> Diagram: CALCULATE takes the slicer's Store filter, replaces it with a Channel equal to Online filter, and evaluates Total Sales for Online only.

## Boolean filter arguments
`CALCULATE([Total Sales], Channel[Channel] = "Online")` is shorthand for filtering ALL values of the column that meet the condition. A Boolean filter REPLACES any existing filter on that same column, so on a table of channels every row shows the Online figure. Boolean filters can name only one column and cannot call a measure. Use IN for a list: `Channel[Channel] IN {"Store", "Online"}`. Combine columns by listing several arguments (they are ANDed) or by using a table filter.

## Table filter arguments and FILTER
When a condition needs a measure or two columns together, pass a table: `CALCULATE([Total Sales], FILTER(ALL(Product[Brand]), [Units Sold] > 1000))`. FILTER iterates a table row by row, so give it the smallest table that works (a single column through ALL or VALUES, not the fact table). `CALCULATE(COUNTROWS(Sales), Sales[SalesAmount] > 10000)` is better than `COUNTROWS(FILTER(Sales, Sales[SalesAmount] > 10000))` because the column filter is compiled efficiently.

## Removing filters: ALL, REMOVEFILTERS, ALLEXCEPT, ALLSELECTED
- `REMOVEFILTERS(Table | Column ...)`: only valid as a CALCULATE filter modifier; clears the filters. Preferred for readability.
- `ALL(Table | Column ...)`: same effect inside CALCULATE, and also works as a table function that returns every row or every distinct value, ignoring filters.
- `ALLEXCEPT(Table, Col1, Col2)`: clears every filter on the table except the columns listed (for "percent of the group" measures).
- `ALLSELECTED(Table | Column)`: restores the filters that came from OUTSIDE the current visual (slicers, page and report filters) and ignores the visual's own rows and columns. This is the "percent of the selection" denominator.
- `ALLNOBLANKROW` leaves out the blank row that a broken relationship can add.
```
% of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS()))   -- ignores everything
% of Selected = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Category)))
% of Category = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Subcategory)))   -- keeps Category filter
```
`REMOVEFILTERS()` with no arguments removes every filter in the model, but keep in mind that it also removes the filters a user chose in slicers.

## KEEPFILTERS
`CALCULATE([Total Sales], KEEPFILTERS(Category[Category] = "Computers"))` intersects the new filter with the existing one instead of replacing it, so categories other than Computers show blank. Use it when the incoming selection must still count, and inside SUMMARIZECOLUMNS and iterator tables so a filter does not widen the rows.

## VALUES, SELECTEDVALUE, HASONEVALUE, ISFILTERED
- `VALUES(Column)` returns the distinct values currently visible in the column (a one-column table, plus a blank row if a relationship has unmatched rows). `DISTINCT` is the same without that blank row.
- `SELECTEDVALUE(Column, [alternate])`: the single visible value, otherwise the alternate (blank by default). The usual tool for slicer-driven logic.
- `HASONEVALUE(Column)`, `ISFILTERED(Column)`, `ISCROSSFILTERED`, `ISINSCOPE(Column)`: test what the context holds. ISINSCOPE is the right way to detect which level of a matrix hierarchy a cell is on, to fix subtotals.

## USERELATIONSHIP, CROSSFILTER, TREATAS
- `CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))` makes an inactive relationship active for that calculation only (role-playing dates: order date versus ship date).
- `CROSSFILTER(Sales[ProductKey], Product[ProductKey], BOTH)` changes the filter direction for that calculation without making the model bidirectional.
- `TREATAS(VALUES(Budget[Month]), Calendar[Date])` applies the values of one column as filters on another with no relationship; it is the way to use a disconnected table or to join on a different grain.

## Evaluation order and common mistakes
- A measure reference like `[Total Sales]` is secretly `CALCULATE(SUM(...))`, so it always converts row context to filters, which is why measures work inside iterators and calculated columns behave differently.
- Replacing versus keeping: a Boolean filter replaces, KEEPFILTERS intersects, ALL removes. When a result "ignores my slicer", you removed it; when it ignores the row, you replaced the row's filter.
- Do not put the SAME column in two Boolean filters expecting OR; two arguments are ANDed (no rows). Use IN or a single `||` expression on one column: `Product[Color] = "Red" || Product[Color] = "Blue"` is allowed when it names one column only.
- A filter on a dimension column does not reach other dimensions unless the fact table connects them. To count products that were ever sold, filter through the fact table.
- Calculation groups and CALCULATE interact through SELECTEDMEASURE; if a measure ignores a calculation item, check precedence.

> **Watch out.** Context transition on a table with duplicate rows can double count, because each duplicate row's filter matches both. Make a unique key or avoid CALCULATE in the iterator.
