I’m getting more and more involved in designing PowerPivot models and want to share a bit of DAX tips in the coming posts. I’ve already touched on DAX in previous posts and in this post I’d like to discuss the new CALCULATE and CALCULATETABLE functions and their uses in a model. DAX is a powerful expression language and one of its main differences from the expression language we know in Excel is the fact that we can reference entire tables rather than cells in a spreadsheet.
When we need to look into a series of values in a PowerPivot table and display it as a column in another table we can use the array of functions that except tables as parameters. Such is the CALCULATETABLE function which excepts a table and a list of filters which can be used as inputs to other functions that will perform calculations against this set of records.
Say, for example that we want to display the total sales amount of a product in a certain year in a PowerPivot report that lists all products. Using the Adventure Works data model, we could add the following calculated column in our products table model:
=SUMX(CALCULATETABLE(FactInternetSales, DimDate[CalendarYear]=2003), FactInternetSales[OrderQuantity])
Note that the CALCULATETABLE function excepts a table as a first parameter and then a filter, using any of the columns related to this table. In this example I chose to filter by year (in a PowerPivot report you could actually make this filter parameterized and let the user choose how to filter the data), but you could also apply more filters to the same expression. Simply add it to the list of filters, separated by a comma.
We then use the SUMX function that receives the subset of the [FactInternetSales] table and applies a SUM function on the [OrderQuantity] column.
There is another DAX function, the RELATEDTABLE function which is a synonym of the CALCULATETABLE function, use can choose to use either of them, the behaviour is similar.
The difference between the CALCULATE function and the CALCULATETABLE function is that the first excepts and returns a single value, whereas the CALCULATETABLE function excepts and returns a table.
I can actually display the same data as showed in the previous example, using the CALCULATE function, like so:
Again, I’m filtering data in the [FactInternetSales] table by year, but this time, I’m first calculating the total sales of the products. The CALCULATE function here receives as an input a single value (total sales, linked by product), applies the date filter and returns a single value for each product.
The question comes to mind when would I use the CALCULATE function and when the CALCULATETABLE function? Firstly remember that they produce different result sets: one produces a single value and the second a table. So when you need to use functions that operate on tables rather than single valued expressions, you need to use the CALCULATETABLE function. And, why would you use this function instead of simply referring to the whole table? The CALCULATETABLE function can apply filters on the table data, so obviously this can be handy in your reports.
I’ll post more on practical uses of this function really soon…
(This is post number 23 for week number 44 of my SQL blog challenge)