1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
variable
  • 8,262
  • 9
  • 95
  • 215

1 Answers1

1

"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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • In calculated column when we use CALCULATE, doesn't the filter context consider expanded table. For example inside ALL you have placed sales. Why not placed location as well? – variable Jan 12 '21 at 04:05
  • The context transition only turns the non-expanded table row context into filter context and I'm assuming `Location` filters `Sales` but not vice versa (i.e. it not a bidirectional filter). – Alexis Olson Jan 12 '21 at 13:49
  • But on sales table it allows me to create a calculated coloumn with formula as: CALCULATE(SUM(..), ALLEXCEPT(location[city]) – variable Jan 12 '21 at 17:49
  • Does that give you the result you’d expect? – Alexis Olson Jan 12 '21 at 18:43