CALCULATE DAX function evaluates an expression in a modified filter context. It comes under Filter DAX function category.

#### Syntax:

CALCULATE( <expression>, <filter1>, <filter2>… )

#### Description:

S no. | Parameter | Description |

1 | expression | The expression to be evaluated. |

2 | filter1,
filter2, — |
(Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions. |

#### Note:

- 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:-

#### Examples:-

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.

hi,

i have 120 years Olympic data file in excel. In that i want to know the young person name with age who got medal. here i just want to know the syntax column_Name = CALCULATE(MAX(‘Table 3′[Name],FILTER(‘Table 3’,’Table 3′[Age])=MIN(‘Table 3′[Age]))) with is syntax i am getting error. can anyone help me.

i)

CALCULATE(

MAX(

‘tablename[columnname] — Year or Age

),

FILTER(

‘Tablename’,’Tablename'[columnname] = ” ”

)

)

Hello! Thank you so much for the post. I’m sorry to bother you, but I have problems with this metric:

On a Card (Visual Objet) I’m using a Calculate (with a Count function and 1 filter). Now, if I apply a data segmentation on it, it does not work, the metric still giving me the result of the Calculate, not the result of the Calculate+Data Segmentation.

I don’t know whats going on.

Thank you!