DAX deep dive
Already writing measures, but not always sure why they return what they do?
Write DAX you can explain, not just copy. A two-day course for people who already build reports in Power BI. You'll learn why your measures return the numbers they do, and how to make them return the ones you need.
You'll get most from it if you've already written a few measures. If you haven't built a report yet, it's too early: start with Power BI fundamentals, or compare all the courses.
What you'll be able to do
- Say why a measure returns the number it does: row context, filter context, and how a star schema (one table of events, such as sales, with lookup tables around it) shapes the filters.
- Write measures with iterators (SUMX and the others) instead of calculated columns.
- Use CALCULATE with FILTER, ALL, ALLEXCEPT and ALLSELECTED, for example for percentage-of-total measures that respond to the slicers you want them to.
- Rewrite long measures with variables, so they're easier to read and check.
- Write DAX queries in DAX query view to see and test table expressions, such as TOPN and VALUES.
- Use time intelligence on a valid date table: previous period, year to date, and change from one period to the next.
“Participants found the training to be one of the best they’ve attended.”
The two days
I explain and demonstrate using data prepared for teaching. You write measures and queries in exercises, with time for questions. Online has the same content and exercises.
Exercises include rewriting a set of long measures with variables.
Day 1: how DAX evaluates a measure
Start from the model: fact tables, lookup tables and star schemas, and why DAX is easiest with a star schema. This isn't a data modelling course.
Columns and measures: how a DAX expression is built, RELATED, implicit measures, SUM, COUNTROWS and DISTINCTCOUNT.
Evaluation context: row context and filter context, why calculated columns are rarely needed, and iterators (SUMX and the others) that bring row context into a measure.
Reading the filter context: SELECTEDVALUE, and parameter tables that let report users switch what a measure shows. Demonstrated, not practised.
CALCULATE: simple true/false filters, then FILTER to add filters and ALL to remove them, for percentage-of-total measures.
Day 2: controlling the filters
More ways to remove filters: ALLEXCEPT and ALLSELECTED, and what a visual's total row is actually calculating.
Blanks and zeros: why a filter for zero also picks up blanks. Demonstrated, not practised.
Variables: rewriting long measures so they're easier to read and check.
Tables in DAX: ALL, FILTER, TOPN and VALUES as tables you can see and test in DAX query view.
Time intelligence: a valid date table with auto date/time turned off, then previous period, year to date and change from one period to the next.
Beyond the star schema: USERELATIONSHIP for inactive relationships, CROSSFILTER instead of two-way relationships, and dynamic titles with VALUES and CONCATENATEX. Demonstrated, not practised.
I'm Alan Harman-Box, and I write and teach every course myself.
I've run this course on site for a team that already used Power BI.
Case study
What to arrange
For dates, setup, payment and cancelling, see the private course terms.
Ask about dates
Tell me you're interested in the DAX deep dive, how many people would come and roughly when.
Or email alan@burningsuit.co.uk.