Length: 1 Day
Learn how to use DAX (Data Analysis Expressions).
Before attending this course, students must have:
- Attended course 20778 or 20779 or have equivalent knowledge.
- Knowledge of relational databases and reporting.
- Some basic knowledge of data warehouse schema topology (including star and snowflake schemas).
- Some exposure to basic programming constructs (such as looping and branching).
- An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.
- Familiarity with Microsoft Office applications – particularly Excel.
WHAT YOU’LL LEARN
After completing this course, students will be able to:
- Understand the use of DAX in different applications
- Choose appropriate functions for use in measures and columns
- Use basic table functions
- Understand evaluation contexts
- Understand the CALCULATE function
- Use Variables for more user-friendly calculations
- Work with iterators such as SUMX
- Write Time intelligence calculations
Module 1: What is DAX
This module introduces the use of DAX in different applications.
- Understanding the data model
- DAX for Excel users, SQL developers, MDX developers and Power BI users
Module 2: Introducing DAX
This module introduces the components of a DAX formula and explains the different uses of measures and calculated columns.
- Understanding DAX calculations
- Understanding calculated columns and measures
- Handling errors in DAX expressions
- Using common DAX functions
Module 3: Using basic table functions
At the end of this module students will be able to write formulas which return a table as the result.
- Understanding FILTER, ALL and ALLEXCEPT
- Understanding VALUES, DISTINCT
- Using tables as scalar values
Module 4: Understanding evaluation contexts
Filter contexts are crucial to understanding what is happening in a DAX formula and ensuring you get the correct result.
- Introducing filter and row contexts
- Using the row context with iterators
- Understanding FILTER, ALL and context interactions
Module 5: Understanding CALCULATE
CALCULATE is perhaps the most important function in DAX, allowing you to work with evaluation contexts appropriately.
- Introducing CALCULATE and CALCULATETABLE
- Understanding context transition
- CALCUALTE rules
Module 6: Variables
Learn how to use Variables to simplify your DAX formulas.
- Introducing VAR syntax
- Understanding the scope of variables
- Common patterns using variables
Module 7: Working with iterators and CALCULATE
Use iterators such as SUMX to meet common reporting needs.
- Using iterators
- Solving common scenarios with iterators
Module 8: Time Intelligence calculations
Understand how to work with dates to write useful time intelligence calculations such as Year to Date and Year Over Year Change.
- Building a date table
- Using year-to-date, quarter-to-date, and month-to-date
- Computing a difference over previous periods
- Computing a moving annual total