CALCULATE is the single most important function in Data Analysis Expression (DAX) because it allows you to manipulate ﬁlter context, which is necessary for building real-world calculations. Let Marco Russo, mentor and book author from sqlbi.com, explain it to you in this 47-minute video.

The CALCULATE function performs two fundamental operations: it directly manipulates the ﬁlter context, and it converts an existing row context into an equivalent ﬁlter context. CALCULATE is important, because it overcomes the limitations of using FILTER and ALL, when they are used to manipulate a ﬁlter context on their own. For example, it is not possible to remove a ﬁlter over a single column in a table, without aﬀecting ﬁlters in other columns, by using ALL on its own. In the demo, you will see that in order to compute a percentage of a total you might need to selectively remove a ﬁlter on some columns of a table without touching ﬁlters on other columns of the same table. You could not achieve that by just using SUMX and ALL! You can control whether a ﬁlter should add to, or replace, the existing ﬁlters at a column granularity level by using CALCULATE with the ALL and VALUES functions, on a column.

The second operation performed by CALCULATE is the transformation of a row context into the correspondent ﬁlter context. This is important for manipulating calculations when you deﬁne a calculated column, or when you specify an expression, inside an iteration function such as SUMX or FILTER. In several practical examples of expressions in a row context, shown in the demo, you will learn that the ﬁlter context is empty when you deﬁne a calculated column, and you can manipulate it by transforming the row context into a ﬁlter context. Because the ﬁlter context automatically propagates to the related tables, you can deﬁne a calculation in a table that automatically ﬁlters rows in any related tables within the CALCULATE scope. It is useful to know that the presence of CALCULATE can be implicit in an expression: whenever a measure is evaluated, CALCULATE is called implicitly to evaluate the expression deﬁned in the measure. For this reason, every measure used in a calculated column, or in an expression within an iteration function, will execute the transformation of the row context into a ﬁlter context.

By watching this 47-minute video, which includes 5 full-length, high-resolution demos, you will be able to use the most important function of DAX. Ideally, you should study this video after you have watched the remaining modules of this course, especially the preceding tutorial about DAX Evaluation Context.

Log in or purchase access below to the premium version of this content.

- Redeem a prepaid code
- Payment is instant and you will receive a tax invoice straight away.
- Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a

- Demo: CALCULATE Overcomes Limitations of ALL (02:18)
- The Syntax of CALCULATE (05:30)
- Filters in CALCULATE (06:30)
- Demo: Using Filters with CALCULATE (08:54)
- Demo: Complex Conditions (19:07)
- CALCULATE Inside Row Context (26:58)
- Demo: Row Context (29:32)
- VALUES in Filter (41:32)
- Demo: VALUES (43:18)
- Summary (47:04)

The Future Series (2019)
Mastering DAX Workshop (Current)
MDX: Multidimensional Expressions
SSAS Cubes
DAX: Data Analysis Expressions (Older)
SQL Server 2012 and SharePoint 2013 BI

Power BI (Current)

- Power BI New Features and Course UpdatesFree
- What is Power BI?Free
- Licensing
- Connecting to Data Sources
- Connecting to Data Sources (2018 Updates)
- Data Import Concepts
- Transforming Data with the Query Editor
- Transforming Data with the Query Editor (2018 Updates)
- Advanced Data Loading
- Modelling Data
- Modelling Data (2018 Updates)
- DAX and the Data Model
- DAX and the Data Model (2018 Updates)
- Power BI Desktop vs Excel
- Cloud and Desktop Power BI Dashboards and Reports
- Power BI Reports (2018 Updates)
- Building Reports in Excel
- Sharing, Distribution, and Security
- Data RefreshFree
- Administration and AuditingFree
- Azure Analysis Services
- Power BI Premium and Power BI EmbeddedFree

Data Mining

- Introduction to Data Mining with Microsoft SQL ServerFree
- Data Mining Concepts and Tools
- Data Mining Model Building, Testing and Predicting with Microsoft SQL Server and Excel
- What Are Decision Trees?Free
- Decision Trees in Depth
- Why Cluster and Segment Data?Free
- Clustering in Depth
- What is Market Basket Analysis?Free
- Association Rules in Depth
- HappyCars Sample Data Set for Learning Data Mining
- Code and Data Samples (R, R Services, SSAS)Free

- MDX Basic ConceptsFree
- MDX QueriesFree
- MDX Calculated Members
- MDX: Member and Set Functions
- What is the MDX Current Member Function?Free
- MDX Numeric Functions and CURRENTMEMBER
- MDX: Previous Period Growths, Shares & Moving Averages
- More MDX Member & Set Functions
- What is a Subselect in MDX?Free
- Exists, MDX Solve Order, and Subselects
- MDX Performance Diagnosis with Profiler

Data Science with Azure Machine Learning

Excel BI for Enterprises

Power BI in Excel 2013

Power BI 2017 (Course Retiring in 2020)

- What is Power BI?Free
- Connecting to Data Sources
- Data Import Concepts
- Transforming Data with the Query Editor
- Advanced Data Loading
- Modelling Data
- DAX and the Data Model
- Power BI Desktop vs Excel
- Cloud and Desktop Power BI Dashboards and Reports
- Building Reports in Excel
- Data RefreshFree
- Administration and AuditingFree

- DAX in Action!Free
- DAX: Calculated Columns vs. MeasuresFree
- Introduction to DAX in Excel
- DAX Evaluation Context
- DAX: The CALCULATE Function
- Querying with DAX
- What is Time Intelligence?Free
- DAX Time Intelligence
- Many-to-many Relationships in DAXFree
- DAX Patterns: Banding, New vs Old, Many-to-many
- How to Calculate with Hierarchies in DAX?Free
- Hierarchies in DAX
- DAX AW Sample Data Set

- Microsoft Business Analytics with Office 2013, SharePoint 2013 and SQL Server 2012
- Introduction to SQL Server 2012 Business Intelligence (Video)
- Introduction to PowerPivot for SQL Server 2012
- Introduction to Power View in SQL Server 2012 Reporting Services
- Introduction to BI Semantic Model & SQL Server 2012 Analysis Services

## Comments

crimiss · 28 June 2013

good day,

after learning dax and implementing tabular models in powerpivot for the past 11 months i have hit a wall regarding a particular problem. i am trying to conduct ABC Analysis (Pareto Analysis) in powerpivot using a dax measure instead of the calculated columns approach. i've created a virtual table but have discovered that there is no row context within the virtual table. in the article on sqlbi.com titled "best practices using summarize and addcolumns" its suggested that a row context can be generated by using the calculate function. any ideas/hints on how to approach this with the SUMX function from within a virtual table? Also, is there a define table syntax similar to the define measure present when using dax queries? Your assistance on this would be greatly appreciated.

Marco Russo · 29 June 2013

The dynamic ABC is possible but can be very slow. You cannot create local virtual tables in a DAX query. The solution is creating a physical table with the three values (A, B, C) and for each one performing a calculation that sums only products of a specific class. The problem is that you should create the limit of each class in the query and this would be repeated too many times, for this reason this could be really really slow.