Who is this course for? DAX is the calculation language of Power BI and is the key to building a powerful analysis. A good knowledge of DAX is essential for Power BI report builder. This course is for data analysts who use Power BI regularly in their work and need to use DAX well.
Learning Objectives: Attendees will be more practised and confident in using DAX, be able to recognise when and where and how to use several common DAX patterns such as time-intelligence and parameter tables and be more familiar with the most common DAX functions.
Pre-requisites: Familiarity with data modelling in Power BI – for example by attending our Data Modelling course.
Course Content: The course will cover the following topics.
- An overview of DAX: language and concepts
- the new DAX Query View, which makes learning and writing DAX more of a pleasure.
- Visual Calculations, which make DAX easier to use: what they are, how and when to use
- DAX Calculated Columns, Measures, Tables.
- Implicit and Explicit measures. Why create explicit measures
- Aggregator Functions e.g. SUM() vs Iterator functions e.g. SUMX
- Common DAX functions
- Variables. The VAR RETURN Syntax. Using several variables. Typical use cases and several examples.
- Context and Filtering. Row context in calculated columns. Implicit Filter Context in Measures. Context transition
- The CALCULATE and CALCULATETABLE functions and why they are so important
- Time Intelligence Why time intelligence is ubiquitous and important. The in-built time-intelligence function e.g. TOTALYTD. More advanced cases e.g. moving average.
- Common DAX Patterns: e.g. percentage of parent and percentage of total; Cumulative (rolling) values; Parameter Table; Moving a column to another table with LOOKUPVALUE.
- Summarising data with table functions. The SUMMARIZE, SUMMARIZECOLUMNS and GROUPBY functions. Examples use cases e.g. double aggregations: max of sum, netting calculations. SELECTCOLUMNS and ADDCOLUMNS
- Joining and generating data. Using CROSSJOIN for cartesian results. Using GENERATE with FILTER to achieve join outside of the standard relationships.
Below are some images from the lab exercises on the course.
