PowerPivot DAX: CALCULATE is a supercharged SUMIF

I can’t believe I didn’t say this last time:  =CALCULATE() is a lot like =SUMIF(), which is a function that Excel gurus know and love…  and sometimes hate :)  SUMIF and its cousins like COUNTIF and the plural SUMIFS are often indispensable.  When you want to perform an aggregation on a table, but just include rows that meet a certain criteria, the SUMIF family is often where you turn.

But SUMIF has a few limitations.  First of all, the conditional syntax is kinda awkward.  Second, if you want an aggregation that is not covered by the functions provided, you are out of luck – there is no MAXIF, for instance.  And you cannot use any of these functions inside a PivotTable, which, when you think about it, would be one of the most useful places to employ them.