If you use Power BI Desktop to connect to data, create and publish reports, you have the necessary skills to attend this course. No previous experience with DAX is necessary, although basic maths skills will come in handy!
While the course will run with Power BI Desktop, the language can also be used for Power Pivot in Excel and SQL Analysis Services (Tabular models).
Overview of DAX - what the DAX language is, where it can be written and suitable data models and relationships
Calculated Columns - the simplest place to start, with lots of power even for this simple task
Measures - this is where a majority of DAX will be used and doesn't have to be complicated to be powerful
Variables - these simplify code and increase its efficiency in leaps and bounds
Table Functions - normally used embedded within other functions rather than in isolation
Evaluation Contexts - this is where the theory starts and the DAX language takes off in terms of flexibility
Calculate - one function to rule them all! Once the evaluation context is understood, this function can manipulate it as required
Blank Row - a core feature of the DAX engine is how it handles less than perfect data models, critical to understand
Dynamic Measures - dynamic titles and detection of different filter states
Nested Evaluation Contexts - navigation through the different evaluation contexts of a single piece of code
Calendar Table - a pre-requisite of any time intelligence calculation
Time Intelligence - anything calculated according to dates, totals year to date, same period last year etc.
DAX Queries - an introduction to using DAX as a query language
Overview
- What is DAX
- Where DAX can be written
- Data models and relationships
Calculated Columns
- Syntax conventions
- RELATED & RELATEDTABLE functions
- IF, SWITCH and CASE functions
- Columns vs Measures
Measures
- Basic aggregation functions
- X aggregators
- FILTER, ALL and DIVIDE functions
- Measure tables
Variables
- Why use variables
- Creating and using variables
Table Functions
- FILTER and ALL
- Testing table functions
Evaluation Contexts
- Row context
- Filter context
- Evaluation context
Calculate
- Adding and Removing filters
- Keeping filters
- Context transition
Blank Row and Avoiding Errors
- Mismatched data and blanks
- COUNTROWS, VALUES, DISTINCT
- ALL, ALLNOBLANKROW
Dynamic Measures
- Dynamic titles
- ISFILTERED
- ALLSELECTED
Nested Evaluation Contexts
- EARLIER function explained
- Banding
- Ranking
Calendar Table
- Why calendar tables are required
- Auto Date Time
- Calendar Auto
- Date functions
- ADDCOLUMNS
Time Intelligence
- TOTAL MTD, QTD and YTD
- CALCULATE and SAMEPERIODLASTYEAR
- Running totals
- Moving averages
- Multiple date relationships
DAX Queries
- Evaluating data
- Filtering and sorting
- Summarising data
The course runs from 9:30am through to 4:30pm.
You will learn in a relaxed environment with a small group of people. A specialist tutor will guide and encourage you through all the learning necessary to develop your skills and questions are always welcome.
Included with your course
We provide the following with online and training centre delivered courses.
- Course notes
- Certificate of attendance
- 90 days post course support
Online Delivery
A link to the online training platform will be sent before the course, simply click the link and follow the instructions.
- See Virtual / Remote training information for more details
- Contact us if you'd like to test the platform before the morning of the course
- You can be located anywhere in the world with an internet connection and your computer
Our Training Centres
For training courses run at one of our training centres we include.
- Free onsite parking
- Lunch and refreshments
- Notepad, pen and Polos
Our Norwich on-site parking (NR3 1UB) is free and situated 5 minutes walk along the river to our training centre at St James Mill. If you arrive by train it is about 20 minutes on the Riverside walk or a short taxi ride to get to us. View full directions for Norwich and all our other venues.