What does CALCULATE do, step by step?
It evaluates an expression in a changed filter context. It starts from the current filters, turns a row context into filters (context transition) if there is one, then applies its filter arguments: each one replaces any existing filter on the same columns, and filters on different columns combine with AND. Then it evaluates the expression.
CALCULATE ( [Total Sales], Product[Category] = "Bikes" )gives bike sales in every cell, whatever category the row shows.What is context transition?
When
CALCULATEruns inside a row context, it turns the current row into a filter on every column of that table. Every measure reference has an implicitCALCULATE, so a measure inside an iterator or a calculated column does this on its own:-- calculated columns on Customer Customer Sales = [Total Sales] Wrong Sales = SUM ( Sales[Amount] )Customer Salesgives each customer's own sales;Wrong Saleshas no filter and repeats the grand total. Context transition over many rows is expensive, and on a table with duplicate rows it filters all the identical rows at once.What is the difference between ALL, ALLEXCEPT, ALLSELECTED and REMOVEFILTERS?
Inside
CALCULATE,ALL ( table or columns )removes the filters on them, andREMOVEFILTERSis the same thing with a clearer name.ALLEXCEPT ( table, columns )removes every filter on the table except those columns.ALLSELECTEDremoves the filters coming from the visual's own rows and columns but keeps the slicer and filter pane selections, so it gives the visible total.ALLcan also be used as a table function;REMOVEFILTERScannot.How do you write a percent of total measure?
Divide by the same measure with the category filter removed:
% of All Categories = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Product[Category] ) ) )Every row is compared with all categories, still within the year or region the reader picked. For the share of what the visual shows after a slicer on category, use
ALLSELECTED ( Product[Category] )instead, so the visible rows add up to 100%.What is the difference between a Boolean filter and FILTER inside CALCULATE?
CALCULATE ( [Sales], Product[Color] = "Red" )is short forFILTER ( ALL ( Product[Color] ), Product[Color] = "Red" ): it scans one column and replaces any filter on color.CALCULATE ( [Sales], FILTER ( Product, Product[Color] = "Red" ) )iterates the product rows that are already filtered, so it keeps the existing filters and is slower on a big table. Use a Boolean filter for column conditions, andFILTERwhen the condition needs a measure or several columns.What does KEEPFILTERS change?
By default a filter argument replaces the existing filter on its columns;
KEEPFILTERSintersects with it instead. In a matrix by color,CALCULATE ( [Sales], Product[Color] IN { "Red", "Blue" } )shows red plus blue sales on every row, Green included. WithKEEPFILTERS ( Product[Color] IN { "Red", "Blue" } ), the Red and Blue rows show their own sales and the other rows are blank. See KEEPFILTERS.How do you count customers who spent more than 1,000?
Iterate the customers and test the measure for each one:
Big Customers = COUNTROWS ( FILTER ( VALUES ( Customer[Customer Key] ), [Total Sales] > 1000 ) )VALUESgives the customers in the current filter context; for each one, the[Total Sales]reference triggers context transition, so it is that customer's total. A Boolean filter cannot do this, because it cannot hold a measure.How do you write an OR condition across two columns in CALCULATE?
Filter arguments on different columns combine with AND, so an OR across columns needs one filter over both columns:
Red or Contoso = CALCULATE ( [Total Sales], FILTER ( ALL ( Product[Color], Product[Brand] ), Product[Color] = "Red" || Product[Brand] = "Contoso" ) )An OR on one column needs no
FILTER:Product[Color] IN { "Red", "Blue" }works directly.What does TREATAS do?
It applies the values of one column as a filter on another column, as if a relationship joined them. That gives a relationship for one measure only, for example between a budget table and Product without a model relationship:
Sales in Budget Categories = CALCULATE ( [Total Sales], TREATAS ( VALUES ( Budget[Category] ), Product[Category] ) )It is the usual way to filter by a disconnected table, and faster than the older
FILTERwithCONTAINSorIN.When would you use CROSSFILTER in a measure?
When one calculation needs a filter to go against the relationship's direction, without setting the relationship to Both for the whole model. For example, how many customers bought the products selected in a slicer:
Customers Who Bought = CALCULATE ( COUNTROWS ( Customer ), CROSSFILTER ( Sales[Customer Key], Customer[Customer Key], BOTH ) )The Product filter reaches Sales, and now Sales filters Customer, but only inside this measure.
Why does a measure total not equal the sum of its rows?
The total row is evaluated in its own filter context, over all the rows at once; it is not a sum of the cells above. For a distinct count that is correct (a customer who bought in two categories is one customer). For logic such as "sales above target, else 0", the total checks the condition once on the grand total. To make the total add the rows, iterate them:
Bonus Total = SUMX ( VALUES ( Product[Category] ), [Bonus] )
CALCULATE and Filter Context Interview Questions
CALCULATE, context transition, the ALL family, KEEPFILTERS, Boolean filters versus FILTER, and the totals that do not add up.