POWER BI: Modeling

The CALCULATE Function

SYNTAX: CALCULATE(expression,filter)

A very useful function is the calculate function. It evaluates an expression which is then modified by filters. You can add any number of filters to the function.

The calculate function can change the context in which the data is filtered. The expression you specify is evaluated using the filter arguments given to the function.

Use the CALCULATE function to sum transactions with revenue greater than $10,000.  Large Transactions = CALCULATE( SUM(TransactionData[Revenue]), TransactionData[Revenue] > 10000 )  The first argument in the calculate function is the Expression that we want to return. After that we can add any number of optional Filter parameters. In this example: • SUM(TransactionData[Revenue]) is the expression that gets returned. • TransactionData[Revenue] > 10000 is the filter Note that the filter columns are not limited to the same table. We can use columns from another table if needed. Just make sure you are using the correct syntax: • 'TableName'[ColumnName] It is optional to have quotations around the table name, ie. TransactionData. However, columns must always be surrounded by square brackets. Multiple Filter Parameters: The example below returns large transactions with medium ad budget:  Large Transactions & Mid_Budget = CALCULATE( SUM(TransactionData[Revenue]), TransactionData[Revenue] > 10000, Advertising[Ad Budget] = "medium" )  In this case the filters are: • TransactionData[Revenue] > 10000 • Advertising[Ad Budget] = "medium" Note that the filters came from different tables. One filter from the TransactionData table and the other from Advertising. The two filters together results in an expression that returns revenue for large transactions and mid size ad spend. Remember, filters act like AND conditions. Using CALCULATE with OR conditions: Although multiple filter parameters act like AND conditions it is also possible to "create" OR conditions in a CALCULATE expression. The example below returns revenue with transactions greater than$10,000 for small of mid size ad spend:

                  Large Transactions & mid_or_small Spend  = CALCULATE(

SUM(TransactionData[Revenue]),
TransactionData[Revenue] > 10000,
)


There is only two filters:

• TransactionData[Revenue] > 10000

But inside the second filter we are using the OR operator to select both small or medium size companies. (recall the || symbol is the OR operator). If I used && (AND operator) instead or had three filters, then the CALCULATE function would return 0. That is because in this data set you cannot have companies that are both small and midsize at the same time.

Using CALCULATE with ALL:

With any of the examples above, adding in slicers in the POWER BI canvas would further filter the data. However, it is possilbe to override the slicer commands using CALCULATE together with the ALL function. This could be useful when you don't want to filter the data any further for a particular slicer or when you want to calculate ratios and percentages.

The example below shows how to override the slicer command for a specific column:

                   Revenue from All_Regions = CALCULATE(

SUM(TransactionData[Revenue]),
ALL(TransactionData[Region])
)


If there is a slicer for the region column in the TransactionData table, then the slicer will be ignored and the full revenue amount will be returned.

The example below will override the filters for any column in the Advertising table

                     All_Ad_Revenue = CALCULATE(

SUM(TransactionData[Revenue]),
)


This will ignore all filters from slicers made from the columns in the Advertising table. However, slicers made from columns in another table will still be filtered. You can filter out as many columns or tables as you like using ALL. Just add them as additional filters.

The easiest way to return total revenue regardless of the filters applied is using ALL on the TransactionData table

                     Total Revenue = CALCULATE(

SUM(TransactionData[Revenue]),
ALL(TransactionData)
)


If there is a slicer for any column in the TransactionData table, then the slicers will all be ignored, and the total revenue will be returned. Note that if there is a slicer made from another column in a different table, that slicer will still be able to filter the data.

Using CALCULATE for percentages

This example shows the ratio of large transactions by Ad Source.

                     % of Large Transactions By Ad Source = DIVIDE(

CALCULATE(SUM(TransactionData[Revenue]),TransactionData[Revenue] > 10000),
0)


SUMMARY:

CALCULATE is an essential DAX function that changes the filter context. This allows powerful expressions to be created, especially when used together with statistical or time-intelligence functions.

CALCULATE function filter Description
TableName[ColumnName] = "value" CALCULATE returns data specified for the colum name and value. Other column filters from slicers can still change the meaure.
ALL(TableName[ColumnName]) CALCULATE returns all of the data specified for the colum name. The same column filter from a slicer will NOT change the measure
ALL(TableName) CALCULATE returns all of the data specified for the entire table. Any other filters from this table will NOT change the measure

Important: filters created in CALCULATE will override any filters created on the canvas from the slicers.

Up Next: Using Logic

• SEO