Using CALCULATE to inject filter context to a calculated column - does it consist related table columns?

Question

Calculated column does not have any filter context.

To inject a filter context we can use the CALCULATE function.

Assume I have a sales, location and date table. Sales is fact. The location and date are dimensions connected to the fact table.

Suppose I create a calculated column in the sales table with CALCULATE(SUM...)), does the injected filter context consist only rows from sales table or does the context also consist all related table columns as well?

A use case for this is for example suppose location table is linked to sales table via locationid and has state, country, then we could use ALLEXCEPT to keep only Country filter so that for each row, the SUM in CALCULATE is aggregated by Country rather than all fields in the row.

Alexis Olson Alexis Olson · Accepted Answer · 2021-01-11T22:40:58

"Inject" isn't quite the right word. Using CALCULATE within a calculated column performs a context transition and then modifies that filter context using whatever arguments (if any) you supply.

You can perform the aggregate you suggest like this:

CALCULATE (
    SUM ( Sales[Amount] ),
    ALL ( Sales ),
    FILTER ( Location, Location[Country] = RELATED ( Location[Country] ) )
)

First, the context transition turns the row context into filter context. Then, the ALL removes any filter context on Sales and the FILTER adjusts the filtering to aggregate at the granularity specified.

ALLEXCEPT doesn't work because Country isn't a column of Sales.

Using CALCULATE to inject filter context to a calculated column - does it consist related table columns?

1 Answers