DAX learning notes

DAX for Power BI & Analytical Models

By Imonikhe Ayeni

This practical DAX reference starts with measures and context, then moves through CALCULATE, iterators, filter modifiers, relationships, time intelligence, rankings, virtual tables, performance and quality assurance.

IntermediateAbout 15 minutesLast updated September 2026Includes interview refresher

A note from the author

I’m Imonikhe Ayeni. I use DAX to build reusable analytical measures in Power BI models.

DAX can look difficult when it is shown without context. Start with simple measures and filter context; the more advanced patterns will make much more sense afterwards.

No matching sections were found. Try a broader search term.

What DAX is

DAX is the analytical expression language used in Power BI semantic models, Power Pivot and related tabular technologies.

Measures

Total Sales = SUM(Sales[SalesAmount])

Calculated columns

Line Value = Sales[Quantity] * Sales[UnitPrice]

DAX syntax and operators

Learn table/column notation, operators, comments and measure naming.

References

Sales[SalesAmount]
[Total Sales]

Variables

Growth % =
VAR CurrentValue = [Sales]
VAR PriorValue = [Sales PY]
RETURN DIVIDE(CurrentValue - PriorValue, PriorValue)

Row context and filter context

Context is the central idea in DAX and explains why the same expression can return different values in different visuals.

Row context

Exists naturally in calculated columns and iterators such as SUMX.

Filter context

Comes from slicers, filters, rows/columns in visuals and DAX expressions.

CALCULATE

CALCULATE evaluates an expression after modifying filter context.

Filtered measure

Blue Sales = CALCULATE([Total Sales], Product[Colour] = "Blue")

Remove a filter

All Region Sales = CALCULATE([Total Sales], REMOVEFILTERS(Region))

Aggregation functions

Use SUM, AVERAGE, MIN, MAX, COUNTROWS and DISTINCTCOUNT for common measures.

Count rows

Transactions = COUNTROWS(Sales)

Distinct count

Customers = DISTINCTCOUNT(Sales[CustomerID])

Iterator functions

Functions ending in X evaluate an expression for each row of a table and then aggregate the results.

SUMX

Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

AVERAGEX

Avg Customer Sales = AVERAGEX(VALUES(Customer[CustomerID]), [Total Sales])

FILTER and filter modifiers

Create table filters and control how existing filters behave.

FILTER

Large Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[SalesAmount] > 1000))

KEEPFILTERS

Premium Sales = CALCULATE([Total Sales], KEEPFILTERS(Product[Tier] = "Premium"))

Relationships in DAX

Use model relationships first; use DAX relationship functions when the calculation genuinely requires them.

Category = RELATED(Product[Category])

USERELATIONSHIP

Shipped Sales = CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]))

Time intelligence

Compare periods using a proper date table and a well-designed model.

Prior year

Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Year to date

Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])

Percentages and share of total

Build ratios with DIVIDE and control denominator context intentionally.

Margin %

Margin % = DIVIDE([Profit], [Total Sales])

Share of total

Share % = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Product[Category])))

Ranking and Top N

Rank entities dynamically and build focused views.

RANKX

Region Rank = RANKX(ALL(Region[Region]), [Total Sales], , DESC, DENSE)

Top-N logic

Combine ranking measures with visual filters or dedicated Top N measures.

Virtual tables

Build temporary tables inside measures to solve advanced analytical problems.

VALUES

Visible Customers = COUNTROWS(VALUES(Customer[CustomerID]))

SUMMARIZE/ADDCOLUMNS

Useful for constructing intermediate grouped tables; use carefully and test semantics.

Context transition

Understand how CALCULATE converts row context into filter context.

Why it matters

Context transition commonly appears when measures are evaluated inside iterators or calculated columns.

Debugging habit

Inspect the table being iterated, the current filters and whether CALCULATE changes them.

Reusable DAX patterns

Build calculations from tested patterns instead of writing each measure from scratch.

Measure branching

Sales = SUM(Sales[SalesAmount])
Sales PY = ...
YoY % = DIVIDE([Sales] - [Sales PY], [Sales PY])

Dynamic labels

Title = "Sales — " & SELECTEDVALUE(Region[Region], "All Regions")

DAX performance and debugging

Write calculations that are correct first, then make them efficient and maintainable.

Use variables

Variables reduce repetition and make intermediate logic easier to inspect.

Optimise the model

Poor relationships or high-cardinality models cannot be fixed by clever DAX alone.

DAX projects and progression

Move from syntax to analytical problem solving.

Intermediate project

Create sales, prior-period, variance, share-of-total, ranking and rolling metrics.

Advanced project

Build a reusable KPI framework with dynamic targets, context-aware labels and drill-through calculations.

DAX key terms and interview refresher

Use this section as a quick revision page before an interview or when a term comes up at work. The aim is to understand the idea well enough to explain it in plain language before memorising syntax.

Filter contextThe set of filters affecting a calculation, created by visuals, slicers, filters and DAX expressions.

Row contextThe current row being evaluated, common in calculated columns and iterator functions.

CALCULATEEvaluates an expression after modifying filter context. It is the central context-changing function in DAX.

Context transitionWhen CALCULATE converts row context into filter context.

IteratorA function such as SUMX or AVERAGEX that evaluates an expression row by row over a table.

Measure branchingBuilding advanced measures from simpler base measures instead of repeating the same logic.

ALL / REMOVEFILTERSFunctions used to clear filters. REMOVEFILTERS is often clearer when the intention is simply to remove filtering.

RELATEDReturns a value from the one-side of an existing relationship while row context is available.

SELECTEDVALUEReturns a single visible value when exactly one exists, otherwise an alternate result.

Time intelligenceCalculations that compare or accumulate values across dates, usually using a dedicated Date table.

Common interview questions

What is the difference between row context and filter context?

Row context means 'which row am I on?'. Filter context means 'which rows are currently visible to this calculation?'. They are different concepts and can exist at the same time.

What does CALCULATE do?

CALCULATE evaluates an expression after changing filter context. It can add, replace or remove filters and can trigger context transition.

What is the difference between SUM and SUMX?

SUM adds values from one column. SUMX iterates a table, evaluates an expression for each row, and then sums the results.

Measure or calculated column?

Use a measure for dynamic report calculations. Use a calculated column for row-level values that must be stored in the model.

What is context transition?

It is the conversion of an existing row context into filter context when CALCULATE is evaluated.

Why use DIVIDE instead of /?

DIVIDE handles zero or blank denominators safely and makes the intended fallback behaviour explicit.

Practical interview tests

These short tasks test whether you can apply the tool, explain your reasoning and validate the result. In a live exercise, say your assumptions aloud and check the output rather than rushing straight to syntax.

Create a reusable total-sales measure.What it tests: base-measure design

Answer: Use an explicit measure with SUM.

Total Sales =
SUM(Sales[SalesAmount])

Calculate each category's share of total sales while keeping other report filters.What it tests: filter context and REMOVEFILTERS

Answer: Divide current-context sales by sales calculated after removing only the category filter.

Why: Removing only the intended filter is safer than clearing more context than necessary.

Category Share % =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(Product[Category])
    )
)

Why can a measure return different values in different rows of the same matrix?What it tests: filter context

Answer: Each row creates a different filter context, so the measure is recalculated for that context.

Why: This is one of the most important DAX concepts to explain clearly.

When would you use SUMX rather than SUM?What it tests: iterators

Answer: Use SUM for one existing numeric column. Use SUMX when an expression must be evaluated row by row and then summed.

Revenue =
SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

Explain CALCULATE in one sentence.What it tests: core DAX understanding

Answer: CALCULATE evaluates an expression after changing filter context.

Why: You can then add that it can add, replace or remove filters and can perform context transition.