Advanced DAX : Calculate Function

So today we are going to learn what are Calculation function in advanced Data Analysis Expression.

Calculate Functions : 

The CALCULATE function is useful because it can modify the filter context of the Expression inside, as indicated by the n number of filter conditions specified by the user, according to these steps:

• If the filter context specified by a filter condition already exists, it will override the already-existing filter context with the new one that’s been specified in the CALCULATE expression.
• If the filter context does not exist, it will add new filter context according to the filter condition specified.

Different types of Calculate Functions : 

• Expanded Tables
• Context Transition
• Evaluation Order 

Let’s discuss each of them now

1.  Expanded Tables : 

• An expanded table contains all the columns of the base table and all the columns of the tables that are linked to the base table through one or more cascading many-to-one or one-to-one relationships.
• Table expansion is a unique concept introduced in DAX, which incorporates the notion of relationships. Though it seems strange in the beginning, it becomes very natural once you get used to it.

Consider the following diagram:

Table expansion has nothing to do with bidirectional filtering. Expansion always happens to the 1-side of a relationship. If you activate the bidirectional cross-filter on a relationship, you are not relying on table expansion. Instead, the engine pushes certain filtering conditions in the code in order to apply the filters on both sides. Thus, in the previous model, if you enable bidirectional cross-filter on the relationship between Sales and Product, this will not add the columns of the Sales table to the expanded Product table.

Each expanded table contains both native and related columns. Native columns are the ones originally present in the table. Related columns are all the columns of related tables, added to the original table through table expansion.

Table expansion does not happen physically. The engine only stores native tables. Nevertheless, the whole DAX semantic is based on the theoretical concept of expanded tables.

RELATED, RELATEDTABLE and table expansion

Table expansion includes the concept of relationship. In fact, a relationship is used when the table is expanded and, once you start thinking in terms of expanded tables, you no longer need to think about relationships.

Consider the RELATED function. When beginning to learn DAX, one typically thinks that RELATED lets you access columns in related tables. A more accurate way of looking at this is that RELATED lets you access the related columns of an expanded table.

2. Context Transition : 

Context transition is an operation performed by CALCULATE and CALCULATETABLE in the definition of the new filter context, under which it evaluates its expression. The formal definition of context transition is easy, but it hides some complexities. In the following description you will see examples based on CALCULATE, but all the concepts can be applied to CALCULATETABLE as well.

CALCULATE :

Transforms all existing row contexts into an equivalent filter context before applying its filter arguments to the original filter context. This happens during the creation of the new filter context to execute its expression parameter.

Example : 

Food_sales =

CALCULATE(

'Sales by Store'[total sales],

FILTER(

ALL('Product Lookup'[product_group]),

'Product Lookup'[product_group] = “Food”))

Output : 

3. Evaluation Order : 

The order of evaluation of the parameters of a function is usually the same as the order of the parameter: the first parameter is evaluated, then the second, then the third, and so on. This is always the case for most of the DAX functions but not for CALCULATE and CALCULATETABLE.

Example : 

1. Food_sales(keepflters) =

CALCULATE(

'Sales by Store'[total sales],

KEEPFILTERS('Product Lookup'[product_group] = "Food"

))

2. Baked Product %(Keepfilter) =

VAR ALLBaked =

CALCULATE(

[Total Baked product],

KEEPFILTERS(

‘Food Inventory’))

VAR Ratio =

DIVIDE(

[Total Baked product],

ALLBaked,

“0”)

RETURN

Ratio

Some Examples and Outputs :

1. Store sales % (REMOVEFILTERS) =

VAR AllStoreSales =

CALCULATE(

[total sales],

REMOVEFILTERS(

'Store Lookup'[store_id]

)

)

VAR Ratio =

DIVIDE(

'Sales by Store'[total sales],

AllStoreSales

)

RETURN

Ratio

2. Store 8 Sales =

CALCULATE('Sales by Store'[total sales],

FILTER(

ALL('Store Lookup'[store_id]),

'Store Lookup'[store_id] = 8)

)

3. Latte_sales =

CALCULATE(

'Sales by Store'[total sales],

'Product Lookup'[product_group] = "Beverages",

'Product Lookup'[product] = "Latte"

)

4. Employees (coffee wrangler) =

CALCULATE('Sales by Store'[total sales],

FILTER(

ALL('Employee Lookup'[position]),

'Employee Lookup'[position]= "coffee wrangler"

)

)

Happy Learning!!!