Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

25 min

DAX foundations: measures, filter context, CALCULATE and variables

Write measures with CALCULATE and variables and predict what they return in a visual.

By the end of this lesson you can

  • Explain filter context and row context, and where each comes from
  • Predict what a CALCULATE measure shows in every cell of a matrix
  • Use VAR and RETURN to make a measure faster and easier to check
  • Write year-to-date, previous-month and previous-year measures

From earlier lessons · 1 of 3

Which storage mode reads Delta tables from OneLake into memory without a scheduled import?

Pick one answer.

From earlier lessons · 2 of 3

A measure must use an inactive relationship between Sales and a ship-date table. Which DAX pattern turns it on for that measure only?

Pick one answer.

From earlier lessons · 3 of 3

What must every DAX query contain?

Pick one answer.

Juniper's finance lead asks for three numbers per region: its share of all sales, Latte's share of its sales, and sales year to date. Your first try at the share shows 100% on every row. The formula looks right. The problem is what DAX thinks each cell is filtered by.

[6]

Start simple: aggregations and DIVIDE

Exam depth: know it cold.

The examples use the Juniper model from lesson 5. Store has Harbour and Old Mill in North, and Riverside in South. Sales has six orders worth 45 in total. Every matrix below has Store[Region] on rows unless it says otherwise.

FunctionReturnsWatch out
SUM ( col )The total of a column.Blank when no rows.
AVERAGE ( col )The mean of a column.Skips blanks but counts zeros.
COUNTROWS ( table )The number of rows.Blank, not 0, for no rows.
DISTINCTCOUNT ( col )The number of different values.Totals don't add up; counts BLANK too.
DIVIDE ( a, b [, alt] )a divided by b.Returns BLANK, or alt, when b is 0 or blank.

Use DIVIDE when the denominator could be 0 or blank. With / you'd have to test it first with IF, and DIVIDE does that faster. If the denominator is a fixed number, like / 100, plain / is fine and a little faster.

Keep DIVIDE's result blank rather than 0. Visuals hide blank rows, so they stay short.

[191][214][215][216]

dax
Total Sales = SUM ( Sales[Amount] ) // North 29 | South 16 | Total 45
Orders = COUNTROWS ( Sales ) // North 4 | South 2 | Total 6
Avg Sale = AVERAGE ( Sales[Amount] ) // North 7.25 | South 8 | Total 7.5
Products Sold = DISTINCTCOUNT ( Sales[Product] ) // North 2 | South 2 | Total 2
Avg Order = DIVIDE ( [Total Sales], [Orders] ) // same as Avg Sale
Five measures and what each shows per region. Products Sold's total is 2, not 4: both regions sell the same two products.

[214][215]

Filter context: what each cell is filtered by

Exam depth: know it cold.

Filter context is the set of filters in force when DAX calculates a measure for one cell. It comes from:

  1. The row and column headers of the visual, such as Region = North.
  2. Slicers, and filters on the visual, page or report.
  3. Relationships, which pass a filter from the one side (Store) to the many side (Sales).
  4. DAX itself, through CALCULATE.

Filters on the same table combine with AND. The grand total row has no Region filter, so it sees all six orders.

A measure doesn't know about rows or cells. It only sees whatever rows of Sales survive the filters. That's why the same SUM ( Sales[Amount] ) gives 29, 16 and 45.

[57][60][61]

Rendering diagram…
The North row and a Latte slicer filter together. Only orders 1 and 3 reach SUM.

[57][60]

Row context and context transition

Exam depth: know it cold.

Row context means the current row. It exists in a calculated column, and inside iterator functions such as SUMX (lesson 21). In a row context, Sales[Amount] means this row's amount.

The catch: row context doesn't filter anything. A calculated column on Store that says SUM ( Sales[Amount] ) sums all of Sales. Every store gets 45.

Context transition fixes this. CALCULATE turns the current row into filter context: Store = Harbour. The relationship then passes that filter to Sales.

Two ways to get it:

  • Wrap the expression in CALCULATE ( ).
  • Call a measure. DAX evaluates every measure reference as if it were wrapped in CALCULATE, so the transition is automatic.

[57][61][197]

dax
// Calculated column on Sales: row context reads this row's values
Unit Price = DIVIDE ( Sales[Amount], Sales[Qty] ) // 4, 3, 4, 4, 3, 3
// Calculated columns on Store Harbour Old Mill Riverside
Sales Plain = SUM ( Sales[Amount] ) // 45 45 45
Sales CT = CALCULATE ( SUM ( Sales[Amount] ) ) // 11 18 16
Sales M = [Total Sales] // 11 18 16
No filter, no transition: every store gets 45. CALCULATE or a measure gives each store its own total.

[57][197]

You are an analytics engineer at Juniper Coffee. You add a calculated column to Store: Store Sales = SUM ( Sales[Amount] ). Every store shows 45. You need each store's own total in the column. What should the formula be?

Pick one answer.

CALCULATE: change the filters, then calculate

Exam depth: know it cold.

CALCULATE ( expression, filter1, filter2, ... ) evaluates the expression in a modified filter context. For each filter argument:

  • If the column isn't filtered yet, CALCULATE adds the filter.
  • If the column is already filtered, CALCULATE replaces that filter with its own.

A simple filter argument like Sales[Product] = "Latte" is a Boolean filter. It may use columns from one table only, and it can't reference a measure.

Filters on other columns, like Region from the matrix row, stay in place. Several filter arguments combine with AND.

[197][217]

dax
Latte Sales = CALCULATE ( [Total Sales], Sales[Product] = "Latte" )
// Matrix: Region on rows, Product on columns, Latte Sales in values
// Latte Muffin Total
// North 20 ?? 20
// South 4 ?? 4
// Total 24 ?? 24
The Region filter stays. The Product filter is added, or replaced if one is there.

[197]

Predict, then reveal

What does Latte Sales show in the Muffin column?

[128][197]

Remove or keep filters: REMOVEFILTERS, ALL, ALLEXCEPT, KEEPFILTERS

Exam depth: know it cold.

Filter modifiers go in CALCULATE's filter arguments and change filters instead of adding new ones.

ModifierWhat it doesTypical use
REMOVEFILTERS ( col or table )Clears filters from that column, or from every column of that table.Percent of total.
ALL ( col or table )The same, as a modifier. It can also return a table.Older measures use it.
ALLEXCEPT ( table, col )Clears every filter on the table except those on the listed columns.Share of a parent, like store within region.
KEEPFILTERS ( filter )Adds the filter without replacing the existing one, so both apply.Respect the user's selection.

Microsoft recommends REMOVEFILTERS over ALL when you only want to clear filters.

[128][197][210][211]

dax
All Regions = CALCULATE ( [Total Sales], REMOVEFILTERS ( Store[Region] ) ) // 45 on every row
% of All = DIVIDE ( [Total Sales], [All Regions] ) // North 64.4% | South 35.6% | Total 100%
// Rows: Region, then Store under it
Region Sales = CALCULATE ( [Total Sales], ALLEXCEPT ( Store, Store[Region] ) )
% of Region = DIVIDE ( [Total Sales], [Region Sales] )
// Harbour 11 / 29 = 37.9% | Old Mill 18 / 29 = 62.1% | Riverside 16 / 16 = 100%
Latte Kept = CALCULATE ( [Total Sales], KEEPFILTERS ( Sales[Product] = "Latte" ) )
// Latte column: 20 | 4 | 24 Muffin column: blank (Latte AND Muffin = no rows)
Each modifier on the Juniper matrix.

[128][210][211]

[210][211][217]

You are an analytics engineer at Juniper Coffee. A matrix has Store[Region] on rows. You need each region's share of sales across all regions, with the total row at 100%. Which denominator should % of All use?

Pick one answer.

[197][210][211]

CALCULATE doesn't ask your slicer's opinion. KEEPFILTERS makes it listen. Picture: Movie-poster parody, dramatic cartoon style with bold lighting.

Variables: VAR ... RETURN

Exam depth: know it cold.

A variable stores the result of an expression under a name. You declare one or more with VAR, then write the result after RETURN. Three reasons to use them:

  1. Readability. A name like LatteSales explains the formula.
  2. Performance. A value used twice is calculated once. Microsoft's example ran in about half the time.
  3. Debugging. Temporarily RETURN one variable to check it.

The rule the exam tests: a variable is calculated once, where it's declared, in the filter context at that point. After that it's a constant. A CALCULATE in the RETURN can't change it.

Names use letters and digits, can't start with a digit and can't contain spaces. A variable can hold a single value or a whole table.

[212][213]

dax
Latte Share =
VAR LatteSales = CALCULATE ( [Total Sales], Sales[Product] = "Latte" )
VAR AllSales = [Total Sales]
RETURN
DIVIDE ( LatteSales, AllSales )
// Debug: comment out the DIVIDE line and RETURN LatteSales: 20 | 4 | 24
// North 20 / 29 = 69.0% | South 4 / 16 = 25.0% | Total 24 / 45 = 53.3%
Two named steps, one division. The commented line is the debugging trick.

[212][213]

A colleague at Juniper Coffee writes: VAR RegionSales = [Total Sales] RETURN DIVIDE ( [Total Sales], CALCULATE ( RegionSales, REMOVEFILTERS ( Store[Region] ) ) ). By Region, it shows 100% on every row. Why?

Pick one answer.

[212][213]

Time intelligence basics

Exam depth: know it cold.

Time intelligence functions shift the date filter: year to date, last month, same period last year. They need a proper date table:

  • One row per day, in a date column with unique values and no blanks or gaps.
  • Whole years. Juniper's 'Date' table runs from 2026-01-01 to 2026-12-31.
  • A relationship from 'Date'[Date] to Sales[Date].
  • Marked with Mark as date table in Desktop, for the classic functions below.

Always pass the date table's column, never the fact table's.

MeasureReturns
TOTALYTD ( expr, 'Date'[Date] )Year to date. Add "6/30" for a year ending June 30.
SAMEPERIODLASTYEAR ( 'Date'[Date] )The same dates, one year back. Use inside CALCULATE.
DATEADD ( 'Date'[Date], -1, MONTH )Dates shifted by n intervals: DAY, MONTH, QUARTER or YEAR.

Exam depth: recognize it. Power BI also has a newer calendar-based time intelligence, in preview. You define calendars on the date table, and functions take the calendar name instead of a column.

[208][218][219][220][221][222][223]

dax
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
Sales PM = CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )
Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY % = DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )
// Rows: 'Date'[Month]
// Month Total YTD PM PY YoY %
// Jan 23 23 blank blank blank
// Feb 22 45 23 blank blank
// Mar blank 45 22 blank blank
// Apr-Dec: YTD 45, every other column blank
No 2025 rows exist, so PY and YoY % stay blank, and DIVIDE keeps them blank instead of failing.

[214][220][221][222]

Try it in Fabric

Test a percent-of-total pattern without changing any model.

  1. Open a semantic model with Write DAX queries (lesson 17).
  2. Pick a table and a text column in it, here T and Col.
  3. Run: DEFINE MEASURE 'T'[All Rows] = CALCULATE ( COUNTROWS ( 'T' ), REMOVEFILTERS ( 'T' ) ) EVALUATE SUMMARIZECOLUMNS ( 'T'[Col], "Rows", COUNTROWS ( 'T' ), "Share", DIVIDE ( COUNTROWS ( 'T' ), [All Rows] ) )
  4. Check that the Share column adds up to 1.
  5. Remove REMOVEFILTERS and run again. Every share becomes 1, the trap from the hook.

[191][211]

Exam-style practice

You are an analytics engineer at Juniper Coffee. Finance runs a fiscal year from July 1 to June 30. The model has a marked 'Date' table related to Sales. You need sales year to date that restarts every July 1. Which measure should you write?

Pick one answer.

[220][223]

You are a BI developer at Juniper Coffee. A matrix has Product on columns. Latte Sales = CALCULATE ( [Total Sales], Sales[Product] = "Latte" ) shows Latte numbers under Muffin too. Managers want blanks outside the Latte column. What should you change?

Pick one answer.

[128][197][217]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[57][128][197][210][213][214][218]

Worth keeping

  1. A measure only sees the rows that survive the cell's filter context.
  2. Row context doesn't filter; CALCULATE or a measure reference turns it into a filter.
  3. CALCULATE replaces filters on the same column; REMOVEFILTERS clears them, KEEPFILTERS keeps them.
  4. Variables are calculated once where declared, so put the filter change inside the VAR.
  5. Time intelligence needs a marked date table and its date column; DIVIDE keeps empty periods blank.

Finance now asks for each store's rank and a running total by month. SUM and CALCULATE can't rank anything. Next lesson: iterators, table filters, window functions and information functions.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (22)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [57]Microsoft Learn: DAX overview
  3. [60]Microsoft Learn: Model relationships in Power BI Desktop
  4. [61]Microsoft Learn: DAX glossary
  5. [128]Microsoft Learn: KEEPFILTERS
  6. [191]Microsoft Learn: Work with DAX query view
  7. [197]Microsoft Learn: CALCULATE
  8. [208]Microsoft Learn: Set and use date tables in Power BI Desktop
  9. [210]Microsoft Learn: ALLEXCEPT
  10. [211]Microsoft Learn: REMOVEFILTERS
  11. [212]Microsoft Learn: VAR
  12. [213]Microsoft Learn: Use variables to improve your DAX formulas
  13. [214]Microsoft Learn: DIVIDE function vs. divide operator (/)
  14. [215]Microsoft Learn: DISTINCTCOUNT
  15. [216]Microsoft Learn: AVERAGE
  16. [217]Microsoft Learn: Avoid using FILTER as a filter argument
  17. [218]Microsoft Learn: Design guidance for date tables in Power BI Desktop
  18. [219]Microsoft Learn: Implement time-based calculations in Power BI
  19. [220]Microsoft Learn: TOTALYTD
  20. [221]Microsoft Learn: SAMEPERIODLASTYEAR
  21. [222]Microsoft Learn: DATEADD
  22. [223]Microsoft Learn: Specify Mark as Date Table for use with time intelligence