CALCULATE.md

CALCULATE

CALCULATE : The most important funtion in DAX

• Evaluate a given expression or formula under a set of defined filters.

CALCULATE ( 
    Expression, // An existing Measure or a DAX Formula for a valid measure.
    Filter1,    // Filter Expressions (Asia[Country] = "India") 
    Filter2     // Filter Expressions (Student[Age] > 18)
)

RELATED works from many to one side.

We can call a column from different table in the expression.

The table should be in relationship

SUMX ( 
    Sales,
    Sales[Quantity] * RELATED ( 'Product'[Unit Price] )
)

Expanded Tables

Expanded tables contain all the columns of the base ( fact table ) table plus all the columns ( lookup table ) on the ONE side of the relationship.

Context Transition

• Process of turning Row context into Filter context.
• By default, Calculated Columns understand Row context but no Filter context.
• To Create Filter context at Row level, CALCULATE is used.

Evaluation Order

Sales of Store 7 (CALCULATE) = 
CALCULATE (
    [Sales],                    // 3rd
    Store[Store ID] = 7,        // 1st Filters are evaluated first.
    Product[Group] = "Gadgets"  // 2nd
)

If the Function contains Modifiers

Sales of Store 7 (CALCULATE) = 
CALCULATE (
    [Sales],                    // 4th
    Store[Store ID] = 7,        // 2nd Filters are evaluated after Modifiers.
    Product[Group] = "Gadgets"  // 3rd
    ALL (                       // 1st Modifier is evaluated first
         Store                  
    )
)

CALCULATE Modifiers

Modify Filters
ALL
ALLSELECTED	Take into account Slicers, Filter Pane, Page level filters and Select only those rows
ALLNOBLANKROW
ALLEXCEPT
KEEPFILTERS
REMOVEFILTERS

ALL is a table function and CALCULATE Modifier.

ALL : Ignores previous existing filters on the table, Return Distinct values if only one column is passed.

KEEPFLTERS : Does not remove an existing column or table filter ( adds new filter context )

REMOVEFILTERS : Removes Filters from the specified tables or columns.

INTERSECT : Multiple filters.

OVERWRITE : Nested filters.

ADDFILTER : Similar KEEPFILTERS

Use Relationships
USERELATIONSHIP	Use ( Inactive ) Relationship

Change Filter Propagation ( Flow )
CROSSFILTER	Between two connected tables