DAX fundamentals

01The problem

Dragging revenue into a visual gives "Sum of revenue", which is fine until you need an average per order line, a count of active customers, or a percentage of the total. Those need DAX (Data Analysis Expressions), Power BI's formula language. DAX looks like Excel, but it thinks in columns and filters, not cells.

02The concept

DAX (Data Analysis Expressions) is the formula language of Power BI. It looks like Excel: functions, brackets, commas. But Excel formulas point at cells, and DAX formulas work on columns and tables, with the answer depending on what's filtered. That one difference is the key to everything in this lesson and the next.

Writing DAX

Every DAX formula has the same shape: a name, an equals sign, and an expression.

Revenue = SUM ( orders[revenue] )

Table names with spaces or special characters need single quotes: 'Date'[Year]. Spaces and line breaks don't matter, so long formulas can be laid out on several lines for readability. Comments start with //.

Calculated columns and measures

DAX can create two different things, and choosing between them is the first decision every time:

Rule of thumb: if it goes in the Values well, make it a measure. If it goes on an axis, in rows or in a slicer, it's a column.

Filter context

A measure has no fixed answer. In a matrix of revenue by region and year, the measure Revenue is calculated once per cell, each time with different filters:

• the row adds a filter (region = Lagos);
• the column adds a filter (Year = 2026);
• slicers, page filters and report filters add more;
• the total row has fewer filters, so it covers more data.

Together these are the cell's filter context. Power BI keeps only the rows of orders that match, then runs the measure on them. For the Lagos 2026 cell, that's 715 order lines, adding up to ₦152,768,595.

"What is filtered right now?" is the question to ask whenever a DAX number surprises you.

Row context and iterators

A calculated column works one row at a time: orders[quantity] means "the quantity in this row". That's row context.

Functions ending in X (SUMX, AVERAGEX, MAXX, COUNTX) are iterators: they create a row context inside a measure. They go through a table row by row, evaluate an expression for each row, then combine the results:

SUMX ( <table>, <expression> )

Revenue = SUMX ( orders, orders[quantity] * orders[unit_price] * ( 1 - orders[discount_pct] / 100 ) )

This gives ₦830,541,245 in total, the same as the Power Query revenue column plus SUM, without storing the column. SUM ( orders[revenue] ) is really shorthand for SUMX ( orders, orders[revenue] ).

The core functions

Aggregations: summarise a column over the current filter context.

Safe division:

DIVIDE ( <numerator>, <denominator> [, <alternate result>] )

DIVIDE ( [Revenue], [Order Lines] ) returns blank instead of an error when the denominator is 0 or blank. Use it instead of / for every ratio; a visual full of errors because one month had no orders is worse than a blank cell.

Logic:

IF ( <test>, <value if true> [, <value if false>] )
SWITCH ( TRUE (), <test1>, <value1>, <test2>, <value2>, …, <else> )

Size Band =
SWITCH (
    TRUE (),
    orders[quantity] >= 20, "Large",
    orders[quantity] >= 10, "Medium",
    "Small"
)

SWITCH ( TRUE (), … ) works like Excel's IFS: it returns the value for the first test that's true. As a calculated column on orders, it gives 1,032 Large, 1,777 Medium and 1,457 Small lines.

Relationships:

For example, a calculated column on customers: Order Lines = COUNTROWS ( RELATEDTABLE ( orders ) ) gives each customer's number of order lines.

Variables

Long measures are easier to read with variables. VAR names an intermediate result; RETURN gives the answer:

Avg Revenue per Line =
VAR TotalRevenue = SUM ( orders[revenue] )
VAR Lines = COUNTROWS ( orders )
RETURN
    DIVIDE ( TotalRevenue, Lines )

Each variable is calculated once, so variables can also make measures faster.

Formatting and organising measures

• Set each measure's format once (Measure tools → Format): whole number with separators for counts, currency for money, percentage for ratios.
• Keep measures together in a dedicated table (the walkthrough shows how).
• Name them as a reader would: Revenue, Order Lines, Active Customers. Avoid Measure 1.

03Example

Four measures every sales report needs:

Revenue = SUM ( orders[revenue] )

Order Lines = COUNTROWS ( orders )

Active Customers = DISTINCTCOUNT ( orders[customer_id] )

Avg Revenue per Line = DIVIDE ( [Revenue], [Order Lines] )

Notice the last one uses the others: measures build on measures. Change Revenue once and everything using it follows.

In a matrix with Date[Year] in Rows, 2025 shows:

The 2026 row is yours to find in the practice. Note that Active Customers for the two years together is 90, not 81 plus the 2026 figure: customers who ordered in both years are counted once in the total.

A calculated column using a relationship:

Category = RELATED ( products[category] )

04Walkthrough

1. Create a table for your measures: Home → Enter data, name it _Measures, load it with its one empty column. (The underscore keeps it at the top of the Data pane.)

2. Select _Measures, then Home → New measure, and type the Revenue measure in the formula bar. Press Enter.

   

3. Add Order Lines, Active Customers and Avg Revenue per Line the same way.

4. Format them: select a measure → Measure tools → set format (Whole number with thousands separator for counts; currency for revenue).

5. Build a Matrix with Date[Year] in Rows and all four measures in Values. Each number is calculated for its year: that's filter context at work. Check the 2025 row against the table above.

6. Add customers[region] to Rows under Year. Each number changes again: now two filters apply to every cell.

7. Delete the empty column in _Measures; the table becomes a measure folder.

Summary

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding