CALCULATE DAX function evaluates an expression in a modified filter context. It comes under Filter DAX function category.
CALCULATE( <expression>, <filter1>, <filter2>… )
|1||expression||The expression to be evaluated.|
|(Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions.|
- CALCULATE is the most often used DAX function in Power BI, this function works as a base function to apply other DAX functions in different scenarios.
- It evaluates the expression given by the user with all the applied filters.
- First argument(expression) returns a value not table, so you can use aggregation function like SUM, MIN, MAX, AVERAGE, COUNTROWS and so on.
So, Let’s start with an example, you can download the sample Dataset from below link:-
Suppose you want to calculate sum of sales for particular Product category, so for this you can use SUMX function because SUMX function support two argument, in first argument you can pass filter condition and in second argument you can pass sales column name.
SUMX with filter = SUMX( Filter(Orders, Orders[Product Category]="Furniture"), Orders[Sales])
However, if you intend to achieve the same task using the “SUM” function, how would you proceed?
Here challenge is that, SUM function only support single argument that is column name.
To overcome this limitation, you can use CALCULATE function with SUM function, as you know it support two argument, expression & filter.
In first argument (expression) you can use aggregation function like sum and in second argument you can use Filter condition.
SUM Measure= CALCULATE( SUM(Orders[Sales]),----Expression Filter(Orders, Orders[Product Category]="Furniture")-- filter ) Output : 660,704,31.
Some other examples of CALCULATE functions:
COUNTROWS = CALCULATE( COUNTROWS(Orders), FILTER(Orders, Orders[Region]="East") ) Output : It will return no of rows under East region.
Distinct Count = CALCULATE( DISTINCTCOUNT( Orders[Product Category]), Filter(Orders, Orders[Region]="EAST" ) )
Output : It will count the distinct product category rows under East region.
Calculate with Multiple filter = CALCULATE( SUM(Orders[Sales]),----Expression Filter(Orders, Orders[Product Category]="Furniture"),-- Filter1 Filter(Orders, Orders[Region]="West")-- Filter 2 )
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.