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 functions the exam tests: iterators, table filters, windows and information functions

Use iterators, table filtering, window functions and information functions in measures.

By the end of this lesson you can

  • Choose SUMX, AVERAGEX, MAXX, COUNTX or RANKX when a total must be built row by row
  • Pick FILTER, ALL, ALLSELECTED, VALUES or TOPN to shape the table a measure works on
  • Read OFFSET, INDEX and WINDOW measures for previous month, first month and running totals
  • Use SELECTEDVALUE, HASONEVALUE, ISINSCOPE and ISFILTERED to make measures react to the visual

From earlier lessons · 1 of 2

A Juniper Coffee measure divides [Revenue] by [Units]. New cafés have no units yet, so the visual shows Infinity. Which measure returns BLANK instead and follows the recommended pattern?

Pick one answer.

From earlier lessons · 2 of 2

A matrix shows Store[Region] on rows. The measure is CALCULATE([Revenue], Store[Region] = "North"). What does the South row show?

Pick one answer.

Juniper Coffee's finance lead opens your report and frowns. Total revenue reads $252, but the till slips add up to $51. The store ranking shows every café as number 1. And the "previous month" column shows next month's numbers. Each bug is one wrong DAX function.

[6]

Iterators: SUMX and friends work row by row

Exam depth: know it cold.

An iterator is a DAX function that walks a table one row at a time. It evaluates an expression for each row, then combines the results. SUMX, AVERAGEX, COUNTX, MAXX, MINX and RANKX are iterators. The X means "per row".

Trace this tiny Juniper Coffee model. Sales has one row per till line. Store has one row per café, related to Sales on Store.

Sales: StoreProductQtyUnitPrice
Harbor.Latte.2.4.
Harbor.Mocha.1.5.
Hilltop.Latte.3.4.
Hilltop.Muffin.2.3.
Riverside.Mocha.4.5.
Store: StoreRegion
Harbor.North.
Hilltop.North.
Riverside.South.

[61][224]

dax
-- Wrong: multiplies two totals. (2+1+3+2+4) * (4+5+4+3+5) = 12 * 21
Revenue Wrong = SUM ( Sales[Qty] ) * SUM ( Sales[UnitPrice] ) -- 252
-- Right: multiplies each row, then adds. 8 + 5 + 12 + 6 + 20
Revenue = SUMX ( Sales, Sales[Qty] * Sales[UnitPrice] ) -- 51
SUM can't multiply per row. SUMX can.

[224]

The table you iterate decides what "per" means. Iterate Sales and you work per till line. Iterate VALUES(Store[Store]), the distinct stores visible in the current filter, and you work per store. Per store, [Revenue] is Harbor 13, Hilltop 18 and Riverside 20.

MeasureResult
AVERAGEX ( Sales, Sales[Qty] * Sales[UnitPrice] ).10.2 (51 / 5 lines).
AVERAGEX ( VALUES ( Store[Store] ), [Revenue] ).17 (51 / 3 stores).
MAXX ( VALUES ( Store[Store] ), [Revenue] ).20 (Riverside).
COUNTX ( VALUES ( Store[Store] ), IF ( [Revenue] > 15, 1 ) ).2 (IF returns BLANK for Harbor, and COUNTX skips blanks).

A measure inside an iterator is evaluated once for each row, through the context transition you met in lesson 20.

[57][225][228]

[57][61]

RANKX: rank against the right list

Exam depth: know it cold.

RANKX(table, expression, [value], [order], [ties]) evaluates the expression for every row of the table. Then it returns the rank of the current value in that list.

  • Order defaults to descending, so the biggest value is rank 1.
  • Ties defaults to Skip: two stores tied at 1 make the next one 3. Dense makes it 2.
  • If the current value isn't in the list, RANKX adds it temporarily and ranks it.

The first argument is the whole game. ALL(Store[Store]) ranks against every café. ALLSELECTED(Store[Store]) ranks only against the cafés picked in slicers. VALUES(Store[Store]) sees just the current row's store.

[226][227]

dax
-- Ranks against all 3 cafés: Riverside 1, Harbor 3
Rank All = RANKX ( ALL ( Store[Store] ), [Revenue] )
-- Ranks against the 2 selected cafés: Riverside 1, Harbor 2
Rank Selected = RANKX ( ALLSELECTED ( Store[Store] ), [Revenue] )
-- Bug: each row sees only its own store, so every row is 1
Rank Broken = RANKX ( VALUES ( Store[Store] ), [Revenue] )
A slicer picks Harbor and Riverside. The matrix has one row per store.

[226][227]

Predict, then reveal

No slicer now. The matrix total row shows 1 for Rank All. Why?

[226]

Table filters: FILTER, ALL, ALLSELECTED, VALUES and TOPN

Exam depth: know it cold.

These functions return a table. You pass that table to CALCULATE or to an iterator.

FunctionReturnsUse it when
FILTER(table, condition).Rows where the condition is true.The test needs a measure, two columns, or OR across columns.
ALL(col or table).All values, ignoring filters.Grand-total denominators, rank against everything.
ALLSELECTED(col or table).Values left by slicers and filters outside the visual.Totals and ranks that respect the user's selection.
VALUES(col).Distinct values visible now.Iterate per store, per month.
TOPN(n, table, orderBy).The top n rows, descending by default.Top 2 stores, top 10 products.

Decision rule: for a simple column test inside CALCULATE, write a Boolean such as Sales[Product] = "Latte". Use FILTER only when the test needs a measure.

[194][217][227][228]

dax
-- Boolean filter on a column: fast. 8 + 12 = 20
Latte Revenue = CALCULATE ( [Revenue], Sales[Product] = "Latte" )
-- Test uses a measure, so FILTER is required. Hilltop 18 + Riverside 20 = 38
Big Store Revenue =
CALCULATE ( [Revenue], FILTER ( VALUES ( Store[Store] ), [Revenue] > 15 ) )
-- Top 2 stores by revenue: Riverside 20 + Hilltop 18 = 38
Top 2 Revenue = CALCULATE ( [Revenue], TOPN ( 2, ALL ( Store[Store] ), [Revenue] ) )
Totals with no slicer. Trace them against the Sales table.

[194][217]

You are an analytics engineer at Juniper Coffee. A slicer picks Harbor (revenue 13) and Riverside (20). Hilltop (18) is not picked. Each store's share must add up to 100% across the selected stores. Which measure should you use?

Pick one answer.

[227]

SUM multiplies the totals. SUMX multiplies each line, then adds. Picture: Whiteboard doodle in black and green marker: on the left, a stick-figure barista multiplies two giant totals on a receipt and gets "252", lo

Window functions: OFFSET, INDEX and WINDOW

Exam depth: know it cold.

Window functions return rows by their position in a sorted list. ORDERBY sorts the list. PARTITIONBY splits it into groups that restart, such as one list per year. You pass the result to CALCULATE or an iterator.

FunctionReturnsJuniper use
OFFSET(delta, ...).One row, delta rows from the current one (-1 = previous).Previous month's revenue.
INDEX(position, ...).One row at a fixed position (1 = first, -1 = last).First month of the year.
WINDOW(from, from_type, to, to_type, ...).A range of rows; ABS = fixed position, REL = relative to the current row.Running total, 3-month average.

The second argument, the relation, is the table of rows to move through. If you leave it out, it defaults to ALLSELECTED of the ORDERBY and PARTITIONBY columns.

[229][230][231]

dax
Prev Month Revenue = -- Jan blank, Feb 10, Mar 14, Apr 9
CALCULATE ( [Revenue],
OFFSET ( -1, ALLSELECTED ( 'Date'[Year], 'Date'[MonthNo] ),
ORDERBY ( 'Date'[MonthNo] ), PARTITIONBY ( 'Date'[Year] ) ) )
Running Revenue = -- 10, 24, 33, 48
SUMX ( WINDOW ( 1, ABS, 0, REL, ALLSELECTED ( 'Date'[Year], 'Date'[MonthNo] ),
ORDERBY ( 'Date'[MonthNo] ), PARTITIONBY ( 'Date'[Year] ) ), [Revenue] )
First Month Revenue = -- 10 on every row
CALCULATE ( [Revenue],
INDEX ( 1, ALLSELECTED ( 'Date'[Year], 'Date'[MonthNo] ),
ORDERBY ( 'Date'[MonthNo] ), PARTITIONBY ( 'Date'[Year] ) ) )
A matrix shows 'Date'[Year] and 'Date'[MonthNo] on rows. Revenue ($k) for 2026: Jan 10, Feb 14, Mar 9, Apr 15.

[229][230][231]

Trace the edges. For January, OFFSET(-1) points before the first row, so it returns an empty table and the measure is BLANK. WINDOW is gentler: a window that starts before the first row is cut to start at the first row. INDEX with a position that doesn't exist, or 0, returns an empty table.

PARTITIONBY('Date'[Year]) is what makes the running total restart each January. Remove it, and December 2025 flows into January 2026.

[229][230][231]

Match

Match each Juniper requirement to the window clause that delivers it.

Pick an item on the left, then its match on the right.

[229][230][231]

You are an analytics engineer at Juniper Coffee. Monthly revenue ($k) for 2026 is Jan 10, Feb 14, Mar 9, Apr 15. A measure uses AVERAGEX(WINDOW(-2, REL, 0, REL, ...), [Revenue]) ordered by month. What does it return for March?

Pick one answer.

[229]

Information functions: ask the visual where you are

Exam depth: know it cold.

Information functions return TRUE, FALSE or a value about the current context. Measures use them to behave differently at different levels.

FunctionReturns
ISBLANK(x).TRUE when x is BLANK.
HASONEVALUE(col).TRUE when exactly one distinct value of col is visible.
SELECTEDVALUE(col, alt).That single value, else alt (BLANK if you omit alt).
ISINSCOPE(col).TRUE when col is the level the visual is grouping by right now.
ISFILTERED(col).TRUE when col is filtered directly. ISCROSSFILTERED also counts filters from related tables.
USERPRINCIPALNAME().The sign-in name of the person running the query, used in dynamic row-level security.

SELECTEDVALUE(col, alt) is a short form of IF(HASONEVALUE(col), VALUES(col), alt).

[61][191][232][233][234][235]

dax
-- Dynamic title: "Revenue for North" or "Revenue for all regions"
Title = "Revenue for " & SELECTEDVALUE ( Store[Region], "all regions" )
-- Rank only on store rows; the total row stays blank
Store Rank = IF ( ISINSCOPE ( Store[Store] ), RANKX ( ALL ( Store[Store] ), [Revenue] ) )
-- RLS rule on Store, if it had a ManagerEmail column (lesson 28)
[ManagerEmail] = USERPRINCIPALNAME ()
Three everyday uses at Juniper Coffee.

[61][232][233]

You are a BI developer at Juniper Coffee. A matrix lists stores with a total row. A store manager slices to Riverside only. The rank must never appear on the total row, even then. Which test should wrap RANKX?

Pick one answer.

[232][233]

Exam-style practice

You are an analytics engineer at Juniper Coffee. Finance wants each month's revenue next to the revenue of the month before, restarting each year. The matrix shows Year and MonthNo. Which filter should you pass to CALCULATE([Revenue], ...)?

Pick one answer.

[229][230][231]

You are an analytics engineer at Juniper Coffee. A measure must return revenue from stores whose [Revenue] is above 15. Which measure works?

Pick one answer.

[217]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[61][194][217][226][227][229][233][234]

Worth keeping

  1. Per-row math needs an iterator; the table you iterate sets the "per".
  2. ALL ranks against everything, ALLSELECTED against the selection, VALUES against only what's visible.
  3. Boolean filters for columns, FILTER only when a measure is in the test.
  4. OFFSET moves relative, INDEX jumps to a position, WINDOW covers a range.
  5. ISINSCOPE knows the visual's level; HASONEVALUE only counts values.

On the job

A store-performance page at Juniper Coffee typically needs five measures from this lesson. A SUMX revenue measure feeds everything. RANKX over ALLSELECTED ranks cafés inside the manager's slicer, wrapped in ISINSCOPE so totals stay clean. OFFSET gives "vs last month", and WINDOW gives a year-to-date line. A SELECTEDVALUE title tells readers which region they're looking at. Before you publish, run each measure in DAX query view with a few store or month rows. Check the numbers by hand on a small slice, exactly as you did here.

[191]

[61]

Finance now wants Current, Last year and YoY % for Revenue, Units and Margin. That's nine near-identical measures, and next month it'll be twelve. Next lesson: calculation groups write the pattern once.

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 (19)

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. [61]Microsoft Learn: DAX glossary
  4. [191]Microsoft Learn: Work with DAX query view
  5. [194]Microsoft Learn: TOPN
  6. [217]Microsoft Learn: Avoid using FILTER as a filter argument
  7. [224]Microsoft Learn: SUMX
  8. [225]Microsoft Learn: COUNTX
  9. [226]Microsoft Learn: RANKX
  10. [227]Microsoft Learn: ALLSELECTED
  11. [228]Microsoft Learn: VALUES
  12. [229]Microsoft Learn: WINDOW
  13. [230]Microsoft Learn: OFFSET
  14. [231]Microsoft Learn: INDEX
  15. [232]Microsoft Learn: ISINSCOPE
  16. [233]Microsoft Learn: SELECTEDVALUE
  17. [234]Microsoft Learn: ISFILTERED
  18. [235]Microsoft Learn: USERPRINCIPALNAME
  19. [236]Microsoft Learn: Appropriate use of error functions