M and DAX — give the model life

How the lesson works

Three things to know before starting, then the lesson content, navigation between lessons, and preparation for practice.

The course's four lessons

The full track. You can jump to any lesson with a click.

→ To the site's homepage

Preparation for practice

The hands-on exercise at the end of the lesson. It's worth opening Power BI Desktop and the AdventureWorks file right now — with the model you built in lesson 2 and the cleanup from lesson 3.

Two languages, two engines

Before writing a single formula — you understand who prepares the data and who analyzes it. That's the split that explains the whole tool.

The M language — the five roles

M It's a functional scripting language that runs behind the scenes of Power Query. Every click in the graphical interface translates into a step written in M — you're writing M even without knowing it.

M syntax — let, in, and case sensitivity

Every query in M is built from a block let … in: a list of steps, each relying on the previous one, and at the end the step to return as the result.

The DAX language — the five roles

DAX — Data Analysis Expressions — is the formula language of the data model. The syntax resembles Excel, but Excel operates on individual cells while DAX operates on entire columns and tables.

M vs. DAX — the comparison table

The same file, two completely different engines. This table is the whole difference.

The golden rule — pushing the processing back

Roche's Maxim: Perform every transformation as far "back" in the chain as possible — as close as possible to the data source.

Three ways to create a table in Power Query

Before moving to DAX — create tables in the editor. Three ways, each for a different situation.

Demo — dimension table via Reference

Derive a dimension table from the fact table — five clicks, without writing a single line of code.

1. Right-click on Sales ← ReferenceA new query is created pointing to Sales as the starting point.
2. Choose ColumnsKeep only the CustomerKey and drop the rest.
3. Remove Rows ← Remove DuplicatesGet a list of distinct customers.
4. Rename ← DimCustomerThe query name is the table name in the model.
5. Close & ApplyThe table loads into the model and is ready to relate.

Date table — the three conditions

This is the most common table analysts create in M — in almost every model you'll build in your career. Without it there's no YoY, no YTD, and no period comparisons.

This is what it looks like on screen — step by step

Four screenshots from the editor — exactly what you will see when you do this yourself.

to enlarge ⤢

to enlarge ⤢

to enlarge ⤢

to enlarge ⤢

What is DAX — and why you can't do without it

The model is connected: 121,253 sales rows, products, customers and dates. The data sits there — but it does not say anything.

Three things DAX knows how to create

three objects, and each has its own place. The difference between them is the most important difference in the lesson.

Column vs. measure — the difference you must understand

The exact same calculation can be written as a column or as a measure — and the result will behave completely differently.

Anatomy of a DAX line

Three parts, always: measure name ← equals sign ← the formula.

Measure name · equals sign · function ( table[column] )

SUM — the first measure

adds up all the values in a single numeric column — into one sum. This is the first measure every analyst writes.

1. Accessing a columnSales[Sales Amount] In the Sales table.
2. Iterates over all 121,253 valuesAn empty cell (Blank) is simply skipped.
3. Returns a single numberThe sum — and a single number is exactly what a KPI visual needs.

AVERAGE and MAX — the same family

Same engine, different ending: AVERAGE returns the average, MAX the highest value.

COUNTROWS — row counting

Counts how many rows are in the table — how many row-transactions passed through the system.

DISTINCTCOUNT vs. COUNT — the critical difference

COUNT counts how many filled cells there are in a column. DISTINCTCOUNT counts how many different There is one. The difference is the difference between "how many rows" and "how many customers".

1. One customer who bought 25 timesCOUNT on CustomerKey will count it 25 times — once per row.
2. DISTINCTCOUNT filters firstRemoves duplicates and only then counts — the customer is counted once.
3. so"How many customers?" = always DISTINCTCOUNT.

DIVIDE — safe division

Divides a number by a number — without crashing when the denominator is zero.

Layered measures — a measure calling a measure

You build a library of measures like Lego: each new measure builds on the previous ones, and formulas aren't duplicated.

Pattern 1 — Arithmetic Between Columns

A new column that calculates a value per row from other columns In the same row. Here you first encounter Row Context.

1. The engine is at row 1Calculates revenue minus cost of that same row only.
2. moves to row 2, 3 … up to 121,253Each row its own value — that's Row Context.
3. The values are stored physicallyYou see them in Data View, like any other column.

Pattern 2 — IF: classification by condition

A column that classifies each row by a condition — yes/no, bigger/smaller.

Pattern 3 — SWITCH: classification tiers

How many classification tiers? SWITCH replaces a ladder of IF-inside-IF — with clean, readable code.

Pattern 4 — RELATED: between tables

Pulls a value from another table into the row — through a relationship you've already built in the model. It's the VLOOKUP of DAX, without the pain.

1. Represents a sales rowReads its ProductKey.
2. Jumps through the *:1 relationshipto the Product table and locates the matching row.
3. Copies the categoryto the sale row — and so on for all 121,253 rows.

Pattern 5 — text concatenation and bucketing

The last two patterns, and both are written in the dimension table — not the fact table.

SUMX — Row-by-Row Calculation

Calculates an expression inside every row — and only then adds up the results. That's what SUM doesn't know how to do.

1. Opens the Sales tableand goes row by row.
2. In each row: quantity × priceThe calculation runs within the context of the single row.
3. Combines the 121,253 resultsand returns a single number.

The gap revealed — $527,508 in hidden discounts

Two numbers, one story — and that is the difference between knowing functions and being an analyst.

Filter Context — one measure, a different value in every cell

we wrote the [Total Sales] once. How does it show four different numbers in one table?

1. Power BI looks at where the cell sitsThe Bikes row? The FY2020 column?
2. Builds a "filter bubble" for the cellOnly the rows relevant to it — that's the Filter Context.
3. Runs the measure inside the bubbleIn the Bikes row, the measure "sees" only bikes.

CALCULATE — the king of DAX

Runs an existing measure — but first changes the filter context according to your conditions. The most important function in DAX.

1. Takes the filters that exist in the cellThe bubble the visual built.
2. Applies the filters you wrote inside itA condition on a column overrides An existing filter on the same column.
3. and only then calculatesThe measure runs inside the new bubble.

CALCULATE is your WHERE — four examples

Four examples from the file, one for each condition type. For SQL speakers, this is exactly the WHERE.

one condition

Two conditions — the comma between them is an automatic AND

Several values in the same column — with IN

a condition from another table — the relationships in the model do the work

ALL — empties the drawer of notes

has-ALL no condition and nowhere to write one. It only sets the starting point: the original table, clean of slicers.

The page has an active Color = "Red" slicer, and it shows $21,597,891

FILTER — the guard that goes row by row

FILTER runs row by row over the table you gave it, and returns a new table — only the rows that evaluated TRUE.

Four shapes, one drawer — the comparison table

The same page, the same slicer Product[Color] = "Red" that shows $21,597,891. The only difference — what's written in CALCULATE's filter argument.

Negation, release and percent of total

Three moves that close out the chapter: negation, release, and percent of the total.

Negation condition

Releasing filters — and what percent of the grand total

The idea — one list, seven questions

Every cell in the table lives alone in a bubble. In the Canada row, the measure only sees Canada — it doesn't know that the US is before it, that Australia is after it, or that it's second out of six. A window function is the tool that lets a cell look at its neighbors in the list.

The ordering row — the list the whole chapter is built on

The six countries in the model, sorted by sales. Each function in the chapter asks a different question about the exact same list.

Seven functions, seven questions

Three rules that prevent 95% of the issues

1 · ORDERBY — what determines the order

ORDERBY is the first decision of every window function: what to order the queue by. It doesn't stand on its own — it's always an argument inside a hosting function.

The same money measure, two reversed words — and two orders

and what happens when you don't write a direction at all

2 · PARTITIONBY — when the count resets

PARTITIONBY takes a single ordering row and breaks it into separate leagues — a competition for each group, with its own place 1. It doesn't sort and doesn't filter; it just determines Where they reset.

From one order row to three leagues

The same helper works across the whole family — here on WINDOW: a running total that resets on every continent

3 · ROWNUMBER — everyone gets their own number

ROWNUMBER goes through the order-by row and sticks a numbered label on each contestant — 1, 2, 3… necessarily unique: even if two contestants are completely tied, each one gets their own number.

Numbering by sales — sorted from the opening

The same function, a different sort key: alphabetical numbering

4 · RANK — rank, not a sequential number

Sounds like ROWNUMBER, but the question is different: not "what's my number in line" but "What place did I get in the competition". The difference shows up in a tie.

The basic sales ranking

and what happens in a tie

Ranking within a group — the five US regions

RANK vs. RANKX — two worlds

The veteran RANKX (2015) and the new RANK (2022) answer the same question — "what place did I get" — with opposite mechanisms. On our model both return an identical ranking, but the edge-case behavior is completely different.

The two minimal syntaxes, side by side

5 · INDEX — who's in position N

Until now, every row asked about itself. INDEX reverses the direction: you set a position — 1 for the first, ‎-1 for the last — and it retrieves the row sitting there.

How much does the champion sell — and how far am I from it

INDEX + PARTITIONBY — the champion of my league

6 · OFFSET — the neighbor, a step up or down

OFFSET moves by a relative distance from me in the sort order: ‎-1 = the row above me, ‎+1 = the row below me. This is the function of "How much do I need to overtake the next in line" — compared to the neighbor, not the champion.

Sales of the country above me

The Lego principle — measure on measure: the gap from the country above me

7 · WINDOW — a segment of the ordered set

Everything we've seen so far returned a single row. WINDOW returns strip: from a starting point to an end point. Every point is either absolute or relative to me (REL) or absolute (ABS).

Running total — from the start of the list up to me

Fixed window — sum of the three largest

The logic of the four numbers

Family summary — seven functions on one line

Seven functions, fourteen examples, and one list. Here's the whole family on the Canada row — so you can see the differences in a single cell.

Chapter map — 20 advanced functions

Twenty functions that pick up where CALCULATE and the window family left off. There are no basic functions here — each one solves a problem that can’t be solved without it.

Fine control over context — KEEPFILTERS · ALLSELECTED · ALLEXCEPT

Three functions that replace ALL's "all or nothing" with precise control: trim instead of overwrite, respect the user's selection, and clear everything except one column.

KEEPFILTERS — to intersect, not override

CALCULATE's default behavior is to override: a condition on a column drops any existing filter on that same column. KEEPFILTERS turns this into an intersection.

On the page there's an active slicer Product[Color] = "Red" showing $21,597,891

and the same idea with a list — here the intersection actually makes sense

ALLSELECTED — respects what the user selected

ALL clears everything, including the slicers. ALLSELECTED clears only the row-level filter — and keeps what the user selected in the report.

Two denominators, two different business questions

ALLEXCEPT — clears everything except…

Removes all filters from a table, except the columns you specified. In practice: "give me the total of the group I belong to".

In the table whose rows have both Group and Country

Relationships at runtime — USERELATIONSHIP · CROSSFILTER · TREATAS

Three functions that touch the relationships themselves — for calculation time only, without changing the model.

USERELATIONSHIP — activates a passive relationship

There can be more than one relationship between two tables, but only one is active. USERELATIONSHIP activates the passive one — within the measure only.

The classic pattern: the same date table, once by order date and once by shipping date

CROSSFILTER — changes filter direction

temporarily changes the filter direction of an existing relationship — Single, Both, or None — only within the calculation.

TREATAS — a virtual relationship

applies values from one table as a filter on a column in another table — even when there is no physical relationship between them.

Query functions — SUMMARIZECOLUMNS · ROLLUPADDISSUBTOTAL

Two functions that live in the query world — DAX Studio, Performance Analyzer, and calculated tables — not inside a regular measure.

SUMMARIZECOLUMNS — the modern aggregation

It groups by columns, adds calculated columns, and accepts filters — all in a single call optimized for VertiPaq. It's the one that actually produces almost every visual in Power BI.

Exactly the table we saw in the window functions chapter

and a filter can be added directly inside it

ROLLUPADDISSUBTOTAL — automatic summary rows

Written inside SUMMARIZECOLUMNS and adds Subtotal and Total rows, with a flag column that tells each row whether it's a summary or a detail.

Table functions — TOPN · EXCEPT · GENERATE

Three functions that return a virtual table — which is then fed into CALCULATE to calculate over just that table.

TOPN — only the top ones

Sum of the three largest countries

EXCEPT — what's here and not there

All countries except the US

GENERATE — a controlled Cross Join

For each row in the first table, the engine re-evaluates the second table — which can use the values of the current row — and returns the combination.

Context and user awareness — ISINSCOPE · SELECTEDVALUE · USERPRINCIPALNAME

Three functions that let a measure know where it is, what's selected, and who's looking at it.

ISINSCOPE — What level am I at?

returns TRUE if the column is at the current level of calculation. This is the tool for writing a measure that behaves differently at the detail row, the subtotal row, and the Total row.

In a matrix where Group is above Country

SELECTEDVALUE — what the user selected

If the context is reduced to a single value — returns it. If not — returns the default. An end to the clunky pattern of IF ( HASONEVALUE ( … ), VALUES ( … ) ).

USERPRINCIPALNAME — who's logged in

Returns the email address of the logged-in user. This is the basis for Dynamic Row-Level Security (Dynamic RLS).

Values, keys, and context — COALESCE · COMBINEVALUES · CONTAINSROW · EARLIER

Four small functions that solve everyday problems: blanks, combined keys, membership checks, and nested row context.

COALESCE — the first value that isn't blank

COMBINEVALUES — Composite Key

CONTAINSROW — does the combination exist

EARLIER — Outer Row Context

In a calculated column you can open a second row context (for example inside FILTER). EARLIER lets you refer to the row's value from the outer context.

A calculated column in the Product table: the product's ranking within its category

Structure and helper data — PATH · PATHITEM · DATATABLE

The last two functions deal with structure: a hierarchy hidden in a flat table, and a table written directly in code.

PATH and PATHITEM — parent-child hierarchy

A flat employee table with EmployeeKey andManagerKey hides an organizational tree inside it. PATH unfolds it into a string, and PATHITEM extracts a level from it.

DATATABLE — a table written in code

Defines a static table directly in DAX, with no external source at all. Perfect for mapping scales, parameter tables, and helper data.

A targets table by category — written once and connected via TREATAS

Chapter summary — 20 functions in one table

Twenty functions, one line each. This is the chapter's orientation table.

Golden rules for writing DAX

Six habits that separate code that works from code that's maintainable.

What we learned — ten points

Quiz — 39 questions on M and DAX

One question at a time, four options, immediate feedback with an explanation. All the questions deal only with M and basic DAX.

DAX Question Bank — 25 Concept Questions

Questions about DAX concepts and syntax — what each function takes, how it behaves, and when to choose it. There are no questions here that require the data file: you can answer all of them without opening Power BI.

Window functions question bank — 22 questions

Questions about the window function family: what each function takes, in what order, and how it behaves. There are no questions here that require the data file.

Advanced DAX Question Bank — 29 Questions

Twenty-nine questions about the twenty advanced functions — syntax, argument order, and behavior. There are no questions here that require the data file.

Syntax bank — 90 questions

Ninety questions, and all of them about syntax: the order of the arguments, parentheses, commas, and what is valid and what is not. There are no questions here about results — only about how the code is written.

Code and errors — 40 syntax questions

Forty questions, and each one opens withA real code snippet. The question is always the same question: what's wrong here, what's missing, and what's the correct order.

Homework — 12 tasks with solutions

All the tasks in Power BI Desktop on AdventureWorks. After every task there's a full solution with the number you should get. The last two tasks are window functions.

Resources and next steps

To go deeper into DAX

• Microsoft Learn — DAX overview
• Microsoft Learn — CALCULATE function
• Microsoft Learn — DAX function reference

To dive deeper into M

• Microsoft Learn — Power Query M reference
• Microsoft Learn — Power Query best practices

Accompanying tools

• DAX function guide — 122 functions: syntax, every argument, an example, and pitfalls
• All the course's questions in one place — 363 multiple-choice questions from all four lessons, with filtering by bank, shuffling, and tracking what you got wrong