Power BI Measures, Calculated Columns and Evaluation Context: Interview-Ready DAX Clarity
A dashboard can look perfect until one slicer changes and the “monthly sales” number suddenly stops making business sense. The difference is rarely the chart - it is whether the analyst understood measures, calculated columns and evaluation context.
- Measures are DAX calculations evaluated at query time, usually responding to slicers, filters and visuals.
- Calculated columns are DAX calculations evaluated row by row during refresh and stored in the model.
- Filter context is the set of filters active when a measure is evaluated - slicers, rows, columns, visual filters and relationships.
- Row context means DAX is evaluating one row at a time - common in calculated columns and iterator functions like
SUMX. - Use measures for aggregations like revenue, margin %, ARPU and growth; use calculated columns for row-level labels like age band, order type or customer segment.
CALCULATEis the power function: it evaluates an expression after modifying filter context.- The safest interview line: “If the result must change with slicers, make it a measure; if it is a row-level attribute needed for slicing, make it a calculated column.”
Big Picture: DAX Has Two Different Moments of Truth
In Power BI, the same-looking formula can behave differently depending on when it runs and what context surrounds it. Calculated columns run when data is loaded or refreshed; measures run when a report visual asks a question.
Core Explanation: The Three Ideas That Make DAX Click
1. Measures answer business questions dynamically
A measure is the right choice when the calculation is an aggregation or KPI: total sales, gross margin %, average order value, customer count, market share or month-on-month growth.
Example DAX:
Total Sales = SUM(Sales[Amount])
Gross Margin % = DIVIDE([Gross Margin], [Total Sales])
The key is that a measure does not hold one fixed value. If your visual is filtered to Maharashtra, it calculates Maharashtra sales. If the slicer changes to Q4, it calculates Q4 sales. If both apply, it calculates sales for Maharashtra in Q4.
2. Calculated columns create row-level attributes
A calculated column is useful when each row needs a stored value that can be used for filtering, grouping, sorting or relationships.
Example DAX:
Order Size Band = IF(Sales[Amount] >= 5000, "Large", "Small")
This is evaluated for every row during refresh. It becomes part of the table, so it can sit on an axis, slicer or relationship path. But it increases model size and does not automatically recalculate based on a user’s slicer choices.
3. Evaluation context is the hidden environment around your formula
Evaluation context means the filters and row positions active when DAX calculates an expression. Most DAX confusion comes from forgetting that Power BI does not calculate in empty space - it calculates inside a context created by the model and the visual.
Worked Example: Same Data, Different Context
Assume a Sales table has four rows:
Create these DAX calculations:
Gross Profit Column = Sales[Amount] - Sales[Cost]
Total Sales = SUM(Sales[Amount])
Gross Profit = SUM(Sales[Gross Profit Column])
Gross Margin % = DIVIDE([Gross Profit], [Total Sales])
The calculated column stores row-level gross profit: 40, 70, 60 and 10. Across all rows, total sales = 500 and gross profit = 180, so gross margin % = 180 / 500 = 36%.
Now apply a slicer for Region = North. The measure recalculates only rows 1 and 2: total sales = 300, gross profit = 110, so gross margin % = 110 / 300 = 36.7%. The calculated column values did not change; the measure result changed because filter context changed.
Measures vs Calculated Columns: The Decision Table
The DAX Context Decision Matrix
If you remember only one application framework, use this matrix. Ask two questions: “Is this row-level?” and “Should it change with slicers?”
Where CALCULATE Fits
CALCULATE is used when the normal filter context is not enough. It evaluates an expression after adding, removing or replacing filters.
Example:
Sales Maharashtra = CALCULATE([Total Sales], Customer[State] = "Maharashtra")
If a visual is filtered to FY2025 and the measure above adds Maharashtra, the result becomes: total sales for FY2025 and Maharashtra. This is why CALCULATE is often the function that separates basic Power BI users from strong DAX users.
Definitions You Must Be Able to Say Cleanly
- Measure: A DAX formula evaluated at query time in the current filter context.
- Calculated column: A DAX formula evaluated row by row during refresh and stored in the model.
- Row context: The current row available to a DAX expression, typically in calculated columns or iterator functions.
- Filter context: Filters applied by visuals, slicers, relationships and DAX functions before evaluating a measure.
- CALCULATE: Microsoft defines it as: “Evaluates an expression in a modified filter context.”
Case Study: Lenskart and the Omnichannel Reporting Trap
Lenskart scaled an omnichannel eyewear model across stores, app, web and eye-test services; its reporting problem shows why DAX context matters.
Lenskart is a useful Power BI case because the business is not a single online funnel. A customer may discover frames online, book an eye test, visit a physical store, use an app offer and complete a purchase through a different channel. That creates a classic analytics challenge: the leadership team wants one version of revenue, conversion and margin, but every team slices the business differently.
The primary reporting move is to build a shared semantic model: fact tables for orders, visits and eye tests; dimension tables for date, city, store, product and channel; and central DAX measures for revenue, conversion and margin. Supporting drivers matter too: clean channel definitions, consistent customer identifiers where permitted, careful relationship design and governance over KPI names.
The lesson: omnichannel analytics fails when every team creates its own numbers. It improves when the model separates business attributes from dynamic KPIs. Lenskart’s broader business strength comes chiefly from its integrated online-offline customer journey, supported by affordable assortment, eye-test access, supply-chain control and technology-led retail operations - not from one dashboard alone.
How AI Changes Power BI Measures, Calculated Columns and Evaluation Context
AI is changing Power BI work in three concrete ways.
- Natural-language DAX drafting: Copilot-style assistants can suggest measures from prompts like “calculate sales for the same period last year.” This speeds up first drafts, but the analyst must still validate filter context, relationship paths and edge cases.
- Semantic model documentation: AI can summarize measure logic, detect duplicate KPI names and draft descriptions for a governed model. This matters because business users often trust dashboards more when every measure has a clear definition.
- Performance troubleshooting: AI can help explain slow DAX patterns, suggest replacing calculated columns with measures, and identify high-cardinality columns that may bloat the model.
Because AI can generate confident but wrong DAX, track these quality measures before using AI-assisted calculations in a business dashboard:
Student workflow: take a Power BI measure list, export definitions into a document, and load it with the business problem into ChatGPT or Claude. Ask: “For each DAX measure, identify the expected filter context, possible ambiguity and one test case.” Then manually test the DAX in Power BI using slicers and Performance Analyzer. AI should accelerate your review - not replace your judgment.
Interview Relevance
“In Power BI, when would you use a measure instead of a calculated column? Explain evaluation context with an example.”
Use the phrase “same formula, different filter context, different answer”. It signals that you understand why Power BI numbers change across visuals.
Common Mistake
The biggest mistake is saying “measures and calculated columns are both just DAX formulas.” That sounds technically true but interview-poor because it ignores timing, storage and context. Fix: always compare them on evaluation time, storage, slicer behavior and business use case.
What to Revise Next
Now that DAX context is clear, move from calculation logic to dashboard delivery. Revise how reports are built, published and shared, then compare Power BI thinking with Tableau’s calculated fields and view-level logic.