(+351) 21 24 10006  ·  info@bconcepts.pt
Carnaxide, Lisbon

DP-600: implement context-dependent calculated columns

João Barros 13 de November de 2026 6 min read

I will teach you the skill of implementing calculated columns that depend on context (filter context / row context) using DAX in a semantic model. This skill is useful for the DP-600 exam because it tests your ability to translate business requirements into efficient DAX logic and, in practice, prevents logical errors and performance issues in reports. By mastering this, you can decide when to materialize values in a model and when to use dynamic measures, which reduces file size and speeds up queries.

What you need to know

Calculated columns are DAX expressions evaluated row by row of a table, but their formula can depend on context (for example, values in other tables or filters applied). It is crucial to distinguish row context (the current row) from filter context (filters coming from the report, slicers or relationships) and know when to use functions like RELATED, CALCULATE and EARLIER / VAR to transform contexts. For example, in a Sales table with 500,000 rows it may be desirable to have a materialized column for SalesWithTax if the rate is fixed by country, because it avoids calculating this in all queries; on the other hand, if the rate varies over time or with report selections, a measure will be preferable.

Simple example: in a Sales table, we want a column SalesWithTax = Sales[Amount] * (1 + TaxRate) where TaxRate comes from the TaxRates table linked by Country. The correct formula needs to use RELATED(TaxRates[Rate]) to get the value by country, because RELATED navigates from the current row in a many table to the corresponding row in the one table. If the TaxRates table has 30 countries with rates between 0.05 and 0.25, RELATED returns the correct value without additional aggregation.

How it works in practice

Follow a practical step-by-step with DAX examples and explanations about context and performance. Assume a model with tables: Sales (SaleID, Date, ProductID, Amount, DiscountPct), Product (ProductID, CategoryID, Price), Category (CategoryID, CategoryName), TaxRates (Country, Rate). Relationships: Sales->Product (many-to-one), Product->Category (many-to-one), Sales->Date (many-to-one). Imagine you have 250,000 rows in Sales and 5,000 products; choices about columns vs measures affect processing time and model size.

1) Basic calculated column — using values in the same row

Sales[AmountWithDiscount] = Sales[Amount] * (1 - Sales[DiscountPct])

Explanation: here there is only row context; the expression is evaluated row by row and is very efficient. If each calculation is simple arithmetic, the cost per row is minimal and the column occupies space proportional to the number of rows (for example, a decimal column on 250k rows can add a few tens of megabytes, depending on the type).

2) Bringing value from another related table — using RELATED

Sales[AmountWithTax] = Sales[Amount] * (1 + RELATED(TaxRates[Rate]))

Explanation: RELATED traverses the many-to-one relationship to fetch TaxRates[Rate] for the Sales row. Use it when there is a direct relationship from Sales to TaxRates (or via Product if needed). If TaxRates has one entry per Country and the relationship is correctly defined, RELATED returns 0.20 (for example) and the calculation works immediately. If there is no relationship, RELATED returns BLANK and you should handle that case with COALESCE or IF to avoid null results.

3) When you need to modify contexts — using CALCULATE

Product[TotalSalesAllYears] = CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales[Date]))

Explanation: in a calculated column on Product we want the total sales of the product without filtering by date; CALCULATE changes the filter context for the calculation. Important: calculated columns are materialized in the model, so be careful if they depend on dynamic filters — measures are usually more appropriate for slicer-dependent logic. If the dataset refreshes daily and has 1,000 products, recalculating this column adds work to the daily processing, so evaluate the trade-off between refresh time and query performance.

4) Avoid EARLIER — use VAR

Category[RankBySales] =
VAR ThisCat = Category[CategoryID]
VAR CatSales =
    CALCULATE(
        SUM(Sales[Amount]),
        FILTER(ALL(Category), Category[CategoryID] = ThisCat)
    )
RETURN
    RANKX(ALL(Category), CatSales, , DESC)

Explanation: EARLIER was used to refer to a previous context in iterations; today VAR + iterator functions (SUMX, FILTER) are clearer and more efficient. It also reduces confusion between row and filter context. For example, for 50 categories, this approach calculates a value per category and then ranks; it is preferable to complex iterations that affect readability and model maintenance.

Common mistakes

1) Confusing calculated columns with measures: columns are calculated at processing and become fixed; measures are evaluated at query time and respect report slicers. Using a column when you want a dynamic response is a mistake. For example, a column that is intended to change depending on the selected year will not — it will always reflect the materialized value.

2) Using RELATED on non-existent relationships or in the wrong direction: RELATED only works from many->one; to navigate one->many use RELATEDTABLE or CALCULATE+SUM. Forgetting this causes errors or null results. Example: trying RELATED(Product[Price]) from Product to Sales returns an error — you must aggregate with SUMX or use RELATEDTABLE to get the many rows and summarize them.

3) Performing calculations that depend on report filters in a calculated column: this creates inconsistency and wrong expectations. If the value must change according to slicers, implement it as a measure instead of a column. Another mistake is not optimizing cardinality: calculated columns with high cardinality (for example, unique values per row) substantially increase model size.

How to practice

Practice creating calculated columns and measures in Power BI Desktop or Fabric (Workspace with Power BI). Follow real scenarios: calculate margins per product using rates in another table; create rankings by category; aggregate historical sales ignoring date filters. Test with datasets of various sizes — 10k, 100k, 500k rows — and observe refresh times and file size. For official assessment, use the Microsoft OFFICIAL and free Practice Assessment and consult the Microsoft study guide for DP-600 — both are free. Do not use dumps; those violate certification rules.

In summary

  • Distinguish row context (columns) from filter context (measures) and choose the correct approach depending on the need for dynamism.
  • Use RELATED to navigate many->one; use RELATEDTABLE or CALCULATE for one->many and aggregate as needed.
  • Prefer VAR + iterator functions over EARLIER for clarity and performance, especially in models with tens to hundreds of categories.
  • Test in Power BI/Fabric with realistic volumes and use the official Practice Assessment and Microsoft study guide to validate your knowledge.