Code never lies, comments sometimes do [Ron Jeffries]

Visitors Map

Follow on Twitter

@ZammaCode
Powered by Blogger.

Tuesday, June 13, 2023

Context in DAX


  • Context describes the environment in which a DAX formula is evaluated. 
  • There are two types of context.
  • Filter Context
    • Filter context is the context that is applied to a whole table or column (set of rows).
    • The filter context is set of filters that are applied to the data model before the evaluation of a DAX statements starts. 
    • Filter context is usually created by visual, slicer, page or whole report. 
    • For instance, the formula AverageSales = AVERAGE(Sales[Total Sales]) will use the filter context to calculate the average of the Total Sales column.
    • The CALCULATE function can be used to change the assigned Filter context.
    • The initial filter context coming from the visual is applied to the underlying table(s) in the data model and automatically propagates from the "one" side of the relationship to the "many" side of the relationship i.e. from the lookup table to the data table.
    • In the following example the filter applied on Products table can propagate downhill to the Sales table but cant not flow back uphill to Customers table.

  • Row Context
    • Row context is the context that is applied to each individual row of a table when a formula is evaluated.
    • The row context is created by default for calculated columns, some formulas like iterators (X functions), and FILTER() function, while all other scenarios will require you to create the row context e.g. in the case of Measures.
    • Row context does not filter the table. To turn the row context into filter context you can use the CALCULATE() function.
    • Row context cannot use relationship, therefore;  RELATED() and RELATEDTABLE() functions allow a row context to leverage an existing relationship.
    • For instance, when evaluating a formula like TotalSales = Sales[Quantity] * Sales[Price], the calculation will be performed for each row, resulting in the TotalSales value for each individual row.

0 comments:

Post a Comment