DAX Fundamentals: Write Reliable Measures in Power BI
DAX Fundamentals is an intermediate-level course of 5 lessons (about 1 h 30 min), with 17 exercises and 15 quiz questions. Prerequisites: Star Schema Modeling. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Power BI
- Intermediate
- 5 lessons
- 1 h 30 min
- 17 exercises
- Windows virtual machine (phase 2)
The course in brief
DAX is the language that brings your Power BI models to life. This course teaches you to tell measures from calculated columns, use aggregations and iterators, handle conditional logic and blank values, then master CALCULATE to modify filters. You finish with variables, RELATED, FILTER and the DAX query view to test your calculations.
What you will be able to do
- Choose between a measure and a calculated column and follow DAX conventions
- Use aggregations, counting functions and iterators (SUMX, AVERAGEX)
- Write conditions with IF and SWITCH and handle BLANK with DIVIDE and COALESCE
- Modify the filter context with CALCULATE, REMOVEFILTERS and KEEPFILTERS
- Structure measures with VAR/RETURN and use RELATED, FILTER and VALUES
- Check measures in the DAX query view
Syllabus: 5 lessons
- Distinguish a measure from a calculated column
- Write a properly named and formatted DAX measure
- Organize measures in the model
- 02
Aggregations and Iterators (SUM, COUNTROWS, SUMX...)
- Use aggregation and counting functions
- Understand how X iterators work
- Choose between simple average and weighted average
- 03
Conditional Logic, DIVIDE and Blank Values
- Write conditions with IF and SWITCH
- Divide without errors using DIVIDE
- Understand and handle BLANK
- 04
CALCULATE: Modifying the Filter Context
- Understand the role of CALCULATE
- Add, replace and remove filters
- Compute a share of the total
Final project
Sales Measures Library
On the sales and targets star model (FaitVentes, FaitObjectifs, DimMagasin, DimProduit, Dates), build a documented measures library for the sales team: revenue, quantities, number of sales, average basket, weighted average price, share of total, sales of a category, target achievement rate with status, and number of products above a threshold. Validate each measure with a DAX query.
Deliverables
- The .pbix file with a _Mesures table organized in display folders
- At least 10 measures, all formatted and commented
- A report page presenting the measures by store and by category
- An acceptance DAX query (EVALUATE SUMMARIZECOLUMNS) with the expected results
Assessment criteria
- Accuracy of the figures (415, 243, 34.58, 35.7%, 83.8%...)
- Relevant choice of measure / calculated column
- Correct use of CALCULATE, REMOVEFILTERS and DIVIDE
- Readability: variables, comments, naming
- Acceptance testing documented in the DAX query view
Frequently asked questions
What is the difference between a measure and a calculated column?
A calculated column is computed row by row at refresh and stored. A measure is computed on demand according to the visual's filters; it is the right choice for totals and ratios.
Why is CALCULATE so important?
CALCULATE is the only function that modifies the filter context of a calculation. It enables shares of the total, comparisons and almost all advanced measures.
Do you need to know Excel to learn DAX?
It isn't required, but it's useful: the syntax resembles Excel formulas. The major difference is that DAX works on tables and columns, not on cells.
Where can I test my DAX formulas?
In table visuals or in the DAX query view of Power BI Desktop, which runs EVALUATE queries and displays the results.
This course, told for your job
Say what you want to be able to do: “DAX Fundamentals” takes its place in your path, with examples from your field. The concepts stay those of the course.