Syntax
CALCULATE(<expression>,<filter1>,<filter2>…)
Tutorial with example
In this tutorial we go through in a series of videos through the function CALCULATE.
Calculate Part1:
Calculate Part 2:
Calculate Part 3: When you should use filter() function or a column in calculate.
Download example file:
Download Power BI file (Part 1): here.
Download Power BI file (Part 2): here.
Download Power BI file (Part 3): here.
Common CALCULATE examples:
1. SUMIF, COUNTIF
CALCULATE is among other things, the function you would use instead of SUMIF or COUNTIF. In this video I will show you how to use CALCULATE as an equivalent of the excel functions SUMIF and COUNTIF.
Download example file:
No download file available for the sumif video. Please use the northwind dataset to practice.
2. RUNNING TOTALS, CUMULATIVE TOTALS
In this video I will show how to calculate running totals or also called cumulative totals using CALCULATE and FILTER:
Download example file:
Download Power BI file (Part 1): here.
Remarks
If the data has been filtered, the CALCULATE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.
Hi Ruth, do you have any video on difference between filter and calculatetable?
KR
Antonio
Hi Antonio,
I just realized that I havent done calculatetable yet.
I will do that first and then a comparison between calculatetable and filter.
Thanks for the idea 🙂
/Ruth
Hi Ruth,
Thank you for making DAX things easier to understand.
I have one request. I checked the DAX Fridays’ video list and also the glossary section of curbal.com but couldn’t find the CALCULATETABLE function. Could you please explain that function?
Thanks & Regards,
Kamal Mukhi
Hi Kamal,
That is true; I need to make that one. Is on my to-do list now 🙂
/Ruth
Hi Ruth, Good insights for me there in your two CALCULATE videos – thanks a lot! Just checking: For the 2nd video – would it be okay if I understood it as – you start with the filter coming from the row filter 1st. Then for FILTER as the second argument in CALCULATE, that this subsequent filtering only happens 2nd. So for example for “Gorgonzola Telino” it starts off with only this row filter so that’s why when you apply CALCULATE’s filter you get a sum for this one only. FILTER in this instance is only iterating over one row of the source table? For the next cheese “Gudbrandsdalsost” the 1st filter per the row results in the “Gudbrandsdalsost” row and the 2nd filter in CALCULATE would result in blank because that’s not “Gorgonzola Telino” or “Queso Cabrales”. Would it be okay to understand it as working that way?