Overview
While working with multiple tables there is often a need to calculate data from one table based on filtered criteria from the context of another table. In this chapter, we will look at the CALCULATE function that deals with this scenario. This is one of the most frequently used functions while working with related tables.
Explanation
In the AdventureWorks tabular model, Sales Territory and Internet Sales are two tables in a one to many relationship through the Sales Territory Key. Let’s say that depending on this key which joins these two tables, we want to find the total of Internet Sales Amount having the Currency Key as 100 which stands for the USD currency.
Open the tabular model and Sales Territory table. Add a calculated field and type in the DAX formula as shown below. When the formula is evaluated, this function selects all the records from the Internet Sales table based on the Sales Territory Key and then aggregates the Sales Amount field where the Currency Key value is 100. Once you enter the formula and click Enter, the values will be calculated and populated.
Based on the context of the Sales Territory table and using filters specific in the DAX formula, values are calculated from the Internet Sales table corresponding to the Internet Sales Amount field. There is one other similar function CALCULATETABLE which can be used for a similar purpose. You can read more about this function here.
Additional Information
- Try to use Summarize, Filter, Sum, RankX and Calculate in the same DAX formula to understand how all these functions can work together in a single formula.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019
