Showing posts with label CALCULATETABLE. Show all posts
Showing posts with label CALCULATETABLE. Show all posts

31 October 2026

⚡️🗒️Power BI: CALCULATE [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 31-Oct-2026

[DAX] CALCULATE

  • {def} function that evaluates an expression in a modified filter context [4]
    • filter arguments are used as the context for evaluating the expression CALCULATE belongs to
      • filter arguments for CALCULATE replace all existing filters over the same columns [6]
      • the new context effected by the filter argument for CALCULATE affects only existing filters on columns mentioned as part of the filter argument [6]
        • filters on columns other than those mentioned in the arguments of CALCULATE remain in effect and unaltered [6]
    • creates a filter context that includes a filter for every column of the iterated table
    • when a DAX formula is executed, multiple levels of filter contexts are combined [2] 
    • requires an active row context [2]
      • either CALCULATE is executed or a measure is invoked [2]
        • implies a context transition in these conditions [2]
          • the row context is transformed into an equivalent filter context [2]
        •  most of the time, the context transition happens because a measure is being invoked rather than because of an explicit CALCULATE [2]
    • it allows developers to
      • add, keep, remove or replace filters
  • {concept} outer filter context
    • the filter context that is active when CALCULATE is invoked[2
  • {concept} inner filter context
    • the new filter context created by CALCULATE and active only during the execution of its first argument [2]
    • leverages the context transition leads to better code [2]
    • propagates through relationships, moving from one table to another [2]
  • {type} implicit CALCULATE [2]
    • functions can conveniently hide CALCULATE or CALCULATETABLE functions from users but still invokes them [2]
      • e.g. TOTALYTD
    • performs a context transition by transforming any existing row contexts into an equivalent filter context
      • the measure referenced is calculated within a filter context created by context transition [2]
      • happens immediately before the measure starts being executed, as if CALCULATE automatically surrounds the measure [2]
  • {type} explicit CALCULATE
    • actively invokes context transition [2]
    • {warning} a random CALCULATE added to the code forces the context transition [2]
      • may lead to unexpected results  
      • {recommendation} don't add CALCULATE unnecessarily [2]
        • context transition can lead to situations in which the calculation works but all control over why it works is lost [2]
  • {benefit} leverage metrics without changing the underlying data by modifying the filter context 
    • allows to create alternative views of the same data model without creating new tables or columns
    • measures become flexible and reusable 
    • because calculations are performed dynamically at query time
  • {benefit} add/remove filters to calculate measures independent of user selections
    • KPIs/indicators can remain stable regardless of report slicers
  • {benefit} simplify complex row-to-aggregation calculations enable context transition
    • allows aggregation logic to operate correctly inside iterators and calculated columns
    • reduces complex manual filtering logic
  • {benefit} use relationships more effectively by activating inactive relationships
    • a single model can support multiple business perspectives without duplicating tables
  • {benefit} create dynamic business metrics 
    • metrics respond intelligently to slicers, filters, drill-downs, and report interactions.
  • {benefit} improve code readability
    • measures become easier to understand, maintain, and troubleshoot
    • using variables and explicit filter modifications makes business logic more transparent to future developers and analysts
  • {benefit} simplify complex DAX
    • functions like CALCULATE, variables, and context manipulation reduce nested expressions and repeated calculations
    • this generally leads to shorter, more maintainable formulas. 
  • {benefit} enable the creation of complex measures that can adapt to changes in filters
    • allows one measure to support many analytical scenarios without requiring multiple versions
    • measures automatically recalculate based on report context, allowing
  • {syntax} CALCULATE(<expression>, <filter1>, <filter2>,...)
    • {parameter} expression to compute
      • only at the end does CALCULATE compute its first argument after creating the new filter context [2]
    • {parameter} filters
      • starting from the second argument, are defined the filters used to create/modify the filter context
        • the filters are applied before the first argument can be computed
          • therefore, they must be evaluated first [2]
      • starts evaluating the 'filter' parameters in the order in which they were provided
      • accepts any number of arguments as filters. They are all intersected, meaning that all the filters work together at once [2]
      • when CALCULATE places a filter on a column that already had filters set by previous functions (or by the visual), the filter placed replaces the previous filter [2] 
        • ⇐ behavior can be changed by using KEEPFILTERS
      • the filter can be one of three types
        • condition
          • one or more values are specified for a set of columns [2]
          • the conditions are tables
            • it evaluates this table and restricts the data context to only the rows that match the rows present in that filter table [2]
        • modifier
          • instructs CALCULATE to perform an action (akabehave in a certain way) 
            • via REMOVEFILTERS or USERELATIONSHIP
        • table
          • filter is applied to the columns present in the table with an IN condition
        • placing a filter on one table automatically propagates its effect to any table with a relationship with the original table, following the cross-filter direction of the relationship [2]
  • {warning} filter context can change unexpectedly
    • CALCULATE modifies the existing filter context before evaluating an expression 
  • {warning} context transition can produce surprising results
    • when CALCULATE is used in a row context (calculated columns, iterators such as SUMX()), it automatically performs context transition
  • {warning} overriding existing filters

  • {warning} performance degradation
    • complex CALCULATE() measures may repeatedly recalculate filter contexts
  • {warning} ambiguous logic from multiple filters
    • the filter arguments can interact in ways that are not intuitive

References:
[1] Greg Deckler & Brett Powell (2024) "Microsoft Power BI Cookbook: Convert raw data into business insights with updated techniques, use cases, and best practices" 3rd Ed.
[2] Alberto Ferrari & Marco Russo (2025) The Definitive Guide to DAX: Mastering the semantic model expression language for Microsoft Power BI, Fabric and Excel
[3] Jeroen ter Heerdt et al (2026) "Microsoft Power BI Visual Calculations: Simplifying DAX"
[4] Microsoft Learn (2026) DAX: CALCULATE [link] 
[5] Cooper Dunn (2025) Microsoft DP-600 Exam Practice Tests: 500 Questions for Implementing Analytics Solutions Using Microsoft Fabric
[6] https://learn.microsoft.com/en-us/dax/keepfilters-function-dax


 

28 October 2026

⚡️🗒️Power BI: DAX Calculations [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 28-Oct-2026

[feature] DAX Calculations

  • {concept} calculated table 
    • {def} table created by users using DAX [3]
      • stored in the data model [3]
      • materialized when the model is refreshed [3]
      • becomes a regular table that can participate in relationships, filtering, and reporting [3]
        •  unlike a visual calculation or a measure [3]
      • enables adding new tables based on data loaded into the model [3]
        • instead of querying and loading values into new table’s columns from a data source [3] 
        • via a DAX formula that defines the table’s values [3]
    • {characteristic} locked after loading
      • after the table is loaded or refreshed, its results are locked in and cannot be changed until the table is refreshed [3]
      • the results are pre-computed and aren’t dynamically determined [3]
    • relatively easy to understand [3]
      • because the DAX statement can be inspected and the results can be predicted [3]
        • the same statement always returns the same result for the same parameters until the table is refreshed [3]
  • {concept} calculated column
    • {def} a column created using a DAX formula
      • behaves like any other column in a table [3]
        • it can be used to define relationships [3]
    • {characteristic} calculated row by row 
      • can be based on 
        • simple formulas (e.g. mathematical operations like addition)
        • complex formulas (e.g. combinations of functions) 
    • {characteristic} stored in the data model
      • ⇒ increases the size of the model [4]
    • {characteristic} calculated during refresh 
      • calculated when the table is first loaded or refreshed [3]
      • can be used in rows, columns, filters, or values of a matrix or any other report [3]
      • {exception}[DirectQuery|composite models] computed during the execution of a query [2]
        • they do not use RAM, but they are also potentially slower [2]
        • they are not evaluated and stored ahead of any query [2] 
    • {characteristic}static
      • its results cannot be changed until the table is refreshed again [3]
    • {characteristic} can be resource-intensive
      • especially for large datasets [4] 
      • {recommendation} should be used judiciously [4]
    • {characteristic} can be used in slicers and filters
      •  help create categories users can interact with
    • {characteristic) can be used for sorting 
      • improves report usability
    • {characteristic} support hierarchies and grouping
      •  helps organize data into business-friendly groups.
    • context transition applies as well [3]
      • where a row context is present, and whenever a function is used that iterates over multiple values (aka iterators) [3]
        • e.g. SUMX 
      • always executed in a row context [2]
        • it's possible to create a row context programmatically [2]
    • {scenario} placing calculated results in a slicer [2]
    • {poor practice} define calculated columns to compute formulas on a row-by-row basis and then aggregate the values [2]
    • moving the computation time at data refresh can benefit the overall user experience [2]
      • many such scenarios can be successfully solved using iterators instead of creating calculated columns [2]
    • categorize text or numbers, especially for filter criteria [2]
  • {concept} measure
    • {def} a DAX calculation that is evaluated at query time based on the current filter context and returns a result based on the current filter context
      • works on sets of rows, producing aggregates of values [2]
      • requires aggregators [2]
      • computed over multiple lines at once [2]
      • does not compute its value on a row-by-row basis [2]
      • work on aggregated data [2] 
      • reusable
      • can reference other measures 
    • {characteristic} calculated only when needed
      • its output is determined not only by its definition but also by the filter context in which it's executed [3]
        •  the same definition can have a different meaning based on the filter context [3]
      •  the same measure returns different results depending on
        • slicers
        • filters 
        • visual selections
        • row/column groups 
    • {characteristic} depends on filter context
      •  evaluated within that filter context and often summarizes multiple rows [3]
    • {characteristic} consumes little storage/memory
      •  only the formula definition, not the results 
    • {characteristic} typically used in visuals
      •  it can be used by other measures, visual-level/page-level/report-level filters
        • used to filter what is displayed
      •  it can be used for 
        • conditional formatting
        • dynamic titles
        • dynamic tooltips 
        • dynamic format strings
        • calculations groups 
        • visual calculations 
    • {characteristic} must be defined in a table
      • a dummy table can be defined for managing (all) measures
      • can be moved in between tables
        • ⇐ a table is merely a container where the measure is organized without affecting its behavior [2]
    • {characteristic} cant be used in relationships
    • {characteristic} can't be used direct in slicer fields 
    • {type} explicit measure
      • created explicitly via the UI 
      • can be created programmatically
        • via Tabular Editor, TMDL, TOM, XMLA Endpoint, Fabric/PBI, CI/CD
        •  
    • {type} implicit measure
      • created (automatically) when a column is added in the values area of a visual [2]
      • not always available
        • they do not work when calculation groups are present in the model or when they are disabled in the Tabular model [2]
      • {downside} do not guarantee full control over the calculation.
    • {benefit} smaller semantic models and lower memory usage
      •  store only the DAX formula and metadata, not a value for every row
    • {benefit} one measures supports multiple scenarios
      • automatically respond to slicers, filters, page/report filters, user selection 
    • {benefit} best performance
      • faster refreshes, smaller model size, better availability 
    • {benefit} reusability 
      •  less code duplication, easier maintenance, consistent business logic
    • {benefit} support advanced analytics solutions
      • time intelligence, running totals, ranking, comparisons, etc. 
    • {benefit} centralize business logic
      • SSoT/consistent logic across reports 
    • {benefit} support modern Power BI features
      • tooltips, conditional formatting, dynamic titles, visual calculations, calculations groups, KPIs, etc.
    • {recommendation} always define an explicit measure instead of using implicit measures [2]
      • allows to modify the global behavior of the calculation without breaking existing reports [2]
    • {recommendation} consider creating measures instead of calculated models
      • measures are preferred whenever a calculation can be express both ways [2]
      • {recommendation} limit the use of calculated columns to the few cases where they are strictly needed [2]
        • increase model size, increase refresh time, consume RAM 
    • {recommendation} consider moving the calculation to the data preparation stage [2]
      • data should be transformed as far upstream as possible and as far downstream as necessary [2]
    • {recommendation} use DAX Studio early
      •  it allows to inspect the query plan, view filter context, analyze storage engine vs formula engine activity, understand why one measure is faster than another, etc. [2]
    • {recommendation} break complex measures into layers [2]
      • allows to validate each step independently

References:
[1] Greg Deckler & Brett Powell (2024) "Microsoft Power BI Cookbook: Convert raw data into business insights with updated techniques, use cases, and best practices" 3rd Ed.
[2] Alberto Ferrari & Marco Russo (2025) The Definitive Guide to DAX: Mastering the semantic model expression language for Microsoft Power BI, Fabric and Excel
[3] Jeroen ter Heerdt et al (2026) "Microsoft Power BI Visual Calculations: Simplifying DAX"
[4] Cooper Dunn (2025) Microsoft DP-600 Exam Practice Tests: 500 Questions for Implementing Analytics Solutions Using Microsoft Fabric

Acronyms:
CI/CD - Continuous Integration/Continuous Development  
DAX - Data Analysis eXpressions
KPI - Key Performance Indicator
RAM - Random Access Memory
SSoT - Single Source of Truth
TMDL - Tabular Model Definition Language
TOM - Tabular Object Model
XMLA - XML for Analysis

 


27 October 2026

⚡️🗒️Power BI: Context [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 28-Oct-2026

[DAX] Context

  • {def} the set of data that a calculation, visual, or DAX expression evaluates at a given moment
    • determines which rows are considered when a calculation is evaluated
      • the same measure can return different results depending on the context
      • enables users to perform dynamic analysis [5]
    • used by measures, calculated columns/tables, iterator functions, slicers & filters, relationships, time intelligence, RLS, etc.
    • can make it difficult to troubleshoot formulas [5]
  • {concept} evaluation context
    • {def} the set of conditions and rules that determines which data is used when a DAX formula calculates a result
    • both the row context and the filter context contribute to the evaluation context [3]
      • the two are very different, serving different purposes, having different features and different behaviors [3]
  • {type} filter context
    • {def} the set of filters that are active when a calculation is evaluated
      • determines which rows of data are visible to a measure at any given moment [2]
      • added when a filter constraint is applied on the set of values allowed in a column or table, by using arguments to a formula [5]
      • applies or removes filters to/from tables in the semantic model [2]
        •  filters tables in a model [2]
        •  propagates filters through relationships [2]
        •  managed by CALCULATE [2]
      • generated not only by the the current visual but also by other visuals [3]
      • each visual has its filter context (aka visual filter context) 
      • determined by all the other visuals in the report that interact with that current visual and all the report filters applied through the filters pane (visual-, page-, and report-level filters) [2]
      • each cell has its filter context (aka cell filter context) 
      • the coordinates of the cell itself determine the filter context of a cell, plus the visual filter context [2]
    • sources
      • visuals (charts, tables, cards, etc.)
      • slicers
      • report/page filters
      • cross-filtering from other visuals
      • DAX functions
        • e.g. CALCULATE(), CALCULATETABLE()
  • {type} row context 
    • {def} the context in DAX where an expression is evaluated for a single row at a time, giving DAX access to the values of the current row
      • aka 'the current row' 
      • used when Power BI is evaluating one row at a time [1]
      • needed to obtain the value of a column for a certain row [2]
        •  {warning} trying to access the value of a column in a measure without an active row context results in an error [2]
      • exists whenever we create a calculated column or whenever we are computing an expression inside an iteration [2]
    • calculated columns have an automatic row context [2]
      • the row context for a formula includes the values from all columns in the current row [
    • automatically follows the relationships between tables to determine which rows in related tables are associated with the current row [5]
      • functions that iterate calculations over a table can have multiple current rows and current row contexts [5]
        •  can be created formulas that recurse over an inner and outer loop [5]
      • certain functions store the row context from the operation that preceded the current operation [5]
        • e.g. EARLIER 
          • the function stores two sets of context in memory [5]
            • one set of context represents the current row for the inner loop of the formula [5]
            • another set of context represents the current row for the outer loop of the formula [5]
    • measures don't have a row context [2] 
      • if is needed to evaluate an expression row by row inside a measure, is needed to start an iteration to create a row context [2]
        • when using iterators, the developer is responsible for creating and handling the row context [2]
          • iterates over a table and evaluate its column values [2]
            • ⇐ one row at a time
            • row context is the result of iterating over rows
              • row context equals iteration [2] is not exactly a correct statement
                • means that DAX evaluates expressions one row at a time
                  • during the iteration, DAX creates a row context that gives the formula access to the values of the current row
                    • functions like SUMX and calculated columns rely on this behavior
                • the two aren't identical 
                  • iteration refers to the process of moving through rows
                  •  row context is knowledge of the current row during that process
              • DAX creates a row context for the current row
            • functions like a cursor [2]
              • during an iteration, it points to a specific row of the table being iterated, telling the DAX formula which row is currently to be considered [2]
    • it does not filter [2][3]
      • it simply indicates to DAX which row is currently responsible for providing the values to perform the calculation and where the result of the calculation should go [3]
        • it's irrelevant whether a column in the table is used in a particular calculation [3]
          • all columns of the table are part of the row context when a calculation is performed [3]
    • encountered when (aka created via)
      • calculated columns
        • the formula defines a calculated column [2]
      • iterators 
        • perform active iteration over a table [2]
          • e.g. SUMX, FILTER, ADDCOLUMNS, etc.
        • scans the table row by row, and during the iteration, the row context points to the current row [2]
          • the row context is needed to obtain the value of a column for a certain row [2]
          • iterator functions create row context while moving through rows
        • help create multiple nested row contexts [2] 
          • increases the complexity of the code [2]
    • can transform into filter context through a mechanism called context transition [3]
    • defines the value of a column by providing the exact row [2]
      • calculated columns are always executed in a row context [2]
      • is not the case for measures [2]
    • {limitation} doesn't propagate filters through relationships from one table to another [2]
      • {recommendation} use RELATED and RELATEDTABLE to direct and move the row context from one table over to another [2]
        • allows to leverage the existing model relationships and the VertiPaq storage engine, avoiding expensive manual lookups and row-by-row filtering operations
    • {limitation} does not place any filter [2]
  • {concept} context transition 
    • occurs when a row context is converted into a filter context [1]
      • takes any active row context and transforms it into a filter in the filter context [3]
      • requires an active row context [2]
        • either CALCULATE is executed or a measure is invoked [2]
          • implies a context transition in these conditions [2]
            • the row context is transformed into an equivalent filter context [2]
          • usually the context transition happens because a measure is being invoked rather than because of an explicit CALCULATE [2]
            •  leveraging the context transition leads to better code [2]
      • without context transition DAX can only see the current row
      • with context transition, DAX can use values from the current row to filter entire tables and compute aggregations
    • the process is automatically execute by CALCULATE and CALCULATETABLE [2]
      • any statement invoking either of these two functions performs a context transition [2]
        • this happens regardless of whether [2]
          • the functions functions were written explicitly [2]
          • CALCULATE or CALCULATETABLE is used explictely [2]
          • a function that conveniently hides CALCULATE or CALCULATETABLE functions from users but still invokes them [2]
            • e.g. TOTALYTD
    • this happens in calculated columns, whenever [2]
      • a row context is present
      • a function that iterates over multiple values is used (aka iterators)
        • SUMX
    • [measures] whenever a measure is invoked, the context transition happens immediately before the measure starts being executed, as if CALCULATE automatically surrounds the measure [2]
    • {benefit} opens new ways to optimize the code [2]
      • eliminates complex lookup logic
      • reduces repeated filtering
      • leverages relationships
      • enables reusable measures
      • improves performance with the storage engine
        • pushes work toward the VertiPaq storage engine
          • ⇐ highly optimized for aggregations
  • {concept} visual calculation [>>]
    • {def} DAX calculation that runs directly on a specific visual (table, matrix, chart, etc.) [3]
  • {concept} visual calculation context
    • context used by visual calculations, where calculations are performed after the visual has already been built
      • uses the rows and columns that are visible in the visual itself rather than directly querying the underlying model
      • allows users to calculate running totals, moving averages, differences, rankings, and other analyses directly within a visual 
  • {recommendation} begin/experiment with simple formulas and relationships to see how context works [5]
    • fastest ways to understand how evaluation context actually works
    • helps to isolate context behavior 
    •  many challenges are related to relationships
    • {recommendation} learn filter context before row context
      •  filter context appears in almost every advanced DAX solution later 
    • {recommendation} predict before you run
      • discoveries/knowledge results especially when the result differs from expectations
    • {recommendation} use simple/small examples
      • facilitates review and troubleshooting
    • {recommendation} build a playground for learning 
      • an experimentation model can prove valuable over time
    • {recommendation} use DAX Studio early
      •  it allows to inspect query plan, view filter context, analyze storage engine vs formula 
  •  

References:
[1] Greg Deckler & Brett Powell (2024) "Microsoft Power BI Cookbook: Convert raw data into business insights with updated techniques, use cases, and best practices" 3rd Ed.
[2] Alberto Ferrari & Marco Russo (2025) The Definitive Guide to DAX: Mastering the semantic model expression language for Microsoft Power BI, Fabric and Excel
[3] Jeroen ter Heerdt et al (2026) "Microsoft Power BI Visual Calculations: Simplifying DAX"
[4] Microsoft Learn (2026) Modify DAX filter context in semantic models [link]
[5] Microsoft Support (202?) Context in DAX Formulas [link]
 
Resources:  
[R1] Microsoft Fabric Community Row vs Filter Context: a Visual Guide [link] 
 
Acronyms:
DAX - Data Analysis eXpressions
RLS - Row Level Security
 
Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 25 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.