Understanding DAX contexts with the CALCULATE function
Data Analysis Expressions (DAX) are used in Power BI, Excel, and other Microsoft data platforms to build sophisticated data models and perform complex calculations otherwise unavailable in default. The CALCULATE function in DAX is one of the most powerful and frequently used functions in Power BI.
DAX is designed to work efficiently with large datasets. It leverages the in-memory processing capabilities of Power BI, ensuring that calculations are performed quickly, even with millions of rows of data. At the heart of DAX are contexts that determine how DAX formulas are evaluated. Understanding contexts in DAX is crucial for creating accurate and efficient data models, as they directly influence the results of calculations.
CALCULATE function
Equations used in Power BI can be influenced by filters or the relationship between the tables in the formula. Context helps perform dynamic analysis in a way that the formula results can change to show cell selection, current row or any other related data (Clark, 2020).
CALCULATE(<expression>, <filter1>, <filter2>, ...)The CALCULATE function first evaluates the filters provided in its arguments, modifies the current filter context based on these filters, and then evaluates the expression within this new context.
CALCULATE can add new filters, modify existing filters, or clear filters. It is the only function in DAX that changes the filter context of an expression. It temporarily replaces the existing filter context with a new one based on the filters provided in the function.
Context
Context in DAX is a foundational concept that shapes how calculations are executed and interpreted. Understanding this concept is vital to harness the full power of DAX in data analysis. Contexts in DAX come in two primary forms: row context and filter context.
Row context
Row context in DAX refers to the context in which a DAX formula is evaluated for each row of a table. It is an essential concept to understand when working with calculated columns, measures, and other DAX expressions in Power BI. In a row context, DAX evaluates an expression for each row in a table individually.
Using this sample file, create a new calculated column with a discount percentage for each product.
Filter context
It refers to a set of filters applied to data before a calculation is performed. The context helps in defining which data subset needs to be excluded or included in a calculation. As the active filters change, the calculation results get adjusted automatically. The filter context is applied to the entire measure and the visibility of the values is adjusted as per the active filters. These filters can be applied by the user through filters, slicers, relationships between data models or any other visual elements. When a filter is added to the filter context it means that all the rows included will be as per the filter context. It is crucial when working with measures or when using functions like CALCULATE that modify or apply additional filters to data.
Similarly using the sample Power BI file, add a new measure to calculate the total units sold in the electronics category in the North region.
Total Electronics Units Sold in North =
CALCULATE(
SUM('transactions'[Quantity]),
'transactions'[Category] = "Electronics",
'transactions'[Region] = "North"
)Result: 33
| Base | Row context | Filter context |
|---|---|---|
| Application | Individual row. | Entire data model |
| Calculation | Row by row | Set of rows as per the filter |
Interaction between Row and Filter Contexts
The interaction between row context and filter context in DAX is a fundamental concept that governs how DAX formulas are evaluated in Power BI. While both row and filter contexts are distinct, they often interact in DAX formulas. Row context allows access to column values within the current row, but it doesn’t automatically filter the entire table. This is important because while the row context is operating on one row at a time, it doesn’t imply that the data model itself is filtered down to just that row.
Total Sales = transactions[Quantity] * transactions[Price]Here, row context exists, allowing DAX to multiply Quantity by Price for each row. However, this operation doesn’t filter the table down to that row; it just calculates the expression for that specific row.
The CALCULATE function bridges the Row and the Filter contexts
The CALCULATE function is used to transition from row context to filter context. This function can take the current row context and turn it into a filter context, applying filters to the entire data model.
Using the sample Power BI file, add a new measure to sum the revenue across all transactions for the north region.
North sales = CALCULATE(
SUM(transactions[Revenue]),transactions[Region] = "North"
)Result: 25K
This calculates the total sales amount only for rows where the region is “North”. The filter context is modified to include only those rows. Furthermore, new filters can also be added to the same equation to further filter the data for the calculation.
Using the sample Power BI file, find the total revenue generated from the electronics category in the North.
East electronic sales = CALCULATE(
SUM(transactions[Revenue]),
transactions[Region] = "East",
transactions[category] = "Electronics"
)Result: 10K
The CALCULATE function also plays an essential role in time intelligence functions, such as calculating year-to-date, quarter-to-date, or month-over-month growth. These calculations often require adjusting the filter context to a specific period.
Using the sample Power BI file, add a new measure to calculate month-to-date sales.
This Month Sales = CALCULATE (
SUM(transactions[Quantity]), DATESMTD(transactions[Order date])
)DATESMTD adds a filter to the date range, so the sales amount is summed only for dates within the month-to-date period.
| Function | Description |
|---|---|
| CLOSINGBALANCEMONTH | Evaluates the expression at the last date of the month in the current context. |
| CLOSINGBALANCEQUARTER | Evaluates the expression at the last date of the quarter in the current context. |
| CLOSINGBALANCEYEAR | Evaluates the expression at the last date of the year in the current context. |
| DATEADD | Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context. |
| DATESBETWEEN | Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date. |
| DATESINPERIOD | Returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date intervals. |
| DATESMTD | Returns a table that contains a column of the dates for the month to date, in the current context. |
| DATESQTD | Returns a table that contains a column of the dates for the quarter to date, in the current context. |
| DATESYTD | Returns a table that contains a column of the dates for the year to date, in the current context. |
| ENDOFMONTH | Returns the last date of the month in the current context for the specified column of dates. |
| ENDOFQUARTER | Returns the last date of the quarter in the current context for the specified column of dates. |
| ENDOFYEAR | Returns the last date of the year in the current context for the specified column of dates. |
| FIRSTDATE | Returns the first date in the current context for the specified column of dates. |
| FIRSTNONBLANK | Returns the first value in the column, column, filtered by the current context, where the expression is not blank |
| LASTDATE | Returns the last date in the current context for the specified column of dates. |
| LASTNONBLANK | Returns the last value in the column, column, filtered by the current context, where the expression is not blank. |
| NEXTDAY | Returns a table that contains a column of all dates from the next day, based on the first date specified in the dates column in the current context. |
| NEXTMONTH | Returns a table that contains a column of all dates from the next month, based on the first date in the dates column in the current context. |
| NEXTQUARTER | Returns a table that contains a column of all dates in the next quarter, based on the first date specified in the dates column, in the current context. |
| NEXTYEAR | Returns a table that contains a column of all dates in the next year, based on the first date in the dates column, in the current context. |
| OPENINGBALANCEMONTH | Evaluates the expression at the first date of the month in the current context. |
| OPENINGBALANCEQUARTER | Evaluates the expression at the first date of the quarter, in the current context. |
| OPENINGBALANCEYEAR | Evaluates the expression at the first date of the year in the current context. |
| PARALLELPERIOD | Returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time. |
| PREVIOUSDAY | Returns a table that contains a column of all dates representing the day that is previous to the first date in the dates column, in the current context. |
| PREVIOUSMONTH | Returns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context. |
| PREVIOUSQUARTER | Returns a table that contains a column of all dates from the previous quarter, based on the first date in the dates column, in the current context. |
| PREVIOUSYEAR | Returns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context. |
| SAMEPERIODLASTYEAR | Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. |
| STARTOFMONTH | Returns the first date of the month in the current context for the specified column of dates. |
| STARTOFQUARTER | Returns the first date of the quarter in the current context for the specified column of dates. |
| STARTOFYEAR | Returns the first date of the year in the current context for the specified column of dates. |
| TOTALMTD | Evaluates the value of the expression for the month to date, in the current context. |
| TOTALQTD | Evaluates the value of the expression for the dates in the quarter to date, in the current context. |
| TOTALYTD | Evaluates the year-to-date value of the expression in the current context. |
Furthermore, to override filters that might have been added, use the ALL function.
Using the sample Power BI file, add a new measure to calculate month-to-date sales.
Total Sales Overall =
CALCULATE(
SUM(transactions[Revenue]),
ALL(transactions[Product])
)The CALCULATE function can also include conditional filters.
Using the sample Power BI file, add a new measure to calculate total sales only for orders where the quantity is greater than 10.
Large order sales = CALCULATE(
SUM(transactions[Revenue]),
transactions[quantity] > 10
)Result: 23K
This filter applies only to rows where the quantity is greater than 10, calculating total sales for these large orders.
Furthermore, the CALCULATE function can also be combined with the filter function.
Calculate total sales for products in the Electronics category where the sales quantity is larger than 10.
Large electronics order sales = CALCULATE(
SUM(transactions[Revenue]),
FILTER(
transactions,
transactions[Category] = "Electronics" && transactions[quantity] > 10
)
)Result: 15K
The FILTER function is used to apply a more complex condition, selecting only rows where the category is “Electronics” and the sales quantity is more than 10.
Using nested CALCULATE function for advanced filtering
In a nested CALCULATE function, every inner function applies its own set of filters, and these filters are applied in a sequence, with each layer potentially overriding the filters applied by the previous layer. The outer CALCULATE can then further modify the context based on its filters. This layering effect allows for intricate control over how data is filtered and aggregated.
CALCULATE(
CALCULATE(
<expression>,
<inner filter>
),
<outer filter>
)Calculate the total sales for a product “Laptop” in a specific region “North” but only for dates in the current month.
Current month total laptop sales in North =
CALCULATE(
CALCULATE(
SUM(transactions[Revenue]),
transactions[Product] = "Laptop"
),
transactions[Region] = "North",
MONTH(transactions[Order date]) = MONTH(TODAY())
)Explanation:
Inner CALCULATE:
SUM(transactions[Revenue]): Calculates the total sales amount.
transactions[Product] = "Laptop": Applies a filter to include only rows where the product is "Laptop".
Outer CALCULATE:
transactions[Region] = "North": Further filters the result to include only sales in the "North" region.
MONTH(transactions[Order date]) = MONTH(TODAY()): Adds another filter to include only sales from the current month.
References
- Clark, D. (2020). Mastering DAX for Power BI: A comprehensive guide to data analysis expressions in Power BI . Apress.
I work as an editor and writer for Project Guru. I have a keen interest in new and upcoming learning and teaching methods. I have worked on numerous scholarly projects in the fields of management, marketing and humanities in the last 10 years. Currently, I am working in the footsteps of the National Education Policy of India to help and support fellow professors to emphasise interdisciplinary research and curriculum design.
I am a Senior Analyst at Project Guru, a research and analytics firm based in Gurugram since 2012. I hold a master’s degree in economics from Amity University (2019). Over 4 years, I have worked on worked on various research projects using a range of research tools like SPSS, STATA, VOSViewer, Python, EVIEWS, and NVIVO. My core strength lies in data analysis related to Economics, Accounting, and Financial Management fields.
Discuss