Rajiv Sharma
Education Desk Staff answered 3 months ago It’s the secret sauce behind powerful calculations and dynamic data modeling in Power BI. Here are the three fundamental concepts that form the backbone of DAX (Data Analysis Expressions):
1. Calculated Columns vs. Measures
Calculated Columns: Created row-by-row during data load. Stored in the data model and used like any other column. Example: FullName = [FirstName] & " " & [LastName]
Measures: Calculated on the fly based on filters and context. Not stored—just computed when needed. Example: TotalSales = SUM(Sales[Amount])
Key Difference: Columns are static per row; measures are dynamic and context-sensitive.
2. Row Context vs. Filter Context
Row Context: Applies when evaluating expressions for each row in a table (e.g., calculated columns).
Filter Context: Comes from filters applied in visuals, slicers, or DAX functions (e.g., CALCULATE).
Understanding how these contexts interact is crucial for writing accurate formulas.
3. Evaluation Context and Context Transition
Evaluation Context: The environment in which a DAX formula is calculated—includes both row and filter context.
Context Transition: Happens when a row context is converted into a filter context, often via functions like CALCULATE.
This is what allows DAX to dynamically adjust calculations based on user interactions.
Education Desk Staff answered 3 months ago Three fundamental concepts of DAX
DAX (Data Analysis Expressions) is the formula language used in Power BI, SSAS Tabular, and Power Pivot. Here are three core concepts that underpin most DAX work:
1) Evaluation context
What it is: The environment in which a DAX expression is evaluated. It determines which rows of a table are visible and how filters affect calculations. Two main types are
Row context: The current row in a table when you reference a column in a calculated column.
Filter context: The set of filters applied to data during evaluation (e.g., slicers, visual filters, or CALCULATE modifiers).
Key takeaway: Understanding how row context and filter context interact is essential for writing correct measures and calculated columns. When you need to modify the filter context, you often use CALCULATE.
2) Measures vs. Calculated columns
Calculated column: Evaluated at data refresh time. Stored in the data model; its value is computed for every row. Useful for row-level computations and relationships.
Measure: Evaluated on demand during query/visual rendering. Not stored; results are calculated based on the current filter context. Useful for aggregations that respond to slicers and filters (e.g., sum, average, percentage of total).
Key takeaway: Prefer measures for dynamic analytics that respond to user interaction; use calculated columns for static row-level calculations.
3) Functions and context transition
Functions: DAX provides a rich set of functions for filtering, aggregation, time intelligence, and more (e.g., SUM, CALCULATE, FILTER, RELATED).
Context transition: Occurs when a row context is converted to a filter context, typically inside CALCULATE or certain iterator functions. This transition enables complex calculations that depend on evaluating expressions in a modified context.
Key takeaway: Mastery of CALCULATE (and its modifier functions like FILTER, ALL, ALLEXCEPT, etc.) is central to building powerful, context-aware DAX expressions.
If you’d like, I can provide simple examples for each concept:
A measure showing a dynamic total that respects filters.
A calculated column that computes a static value per row.
A snippet demonstrating a context transition with CALCULATE.
Please login or Register to submit your answer