Power BI – DAX

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.

Course Length: 2 days £2,900 ex VAT or 3 days £4,200 ex VAT

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.

  • DAX Calculated Columns, Measures, Tables. Implicit and 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.
  • 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.
  • Common DAX Patterns: Percentage of parent and percentage of total; Cumulative (rolling) values; Parameter Table; Moving a column to another table with LOOKUPVALUE.
  • Introduction to DAX Studio, a free popular tool for writing and running DAX queries.

Below are some images from the lab exercises on the course.

DAX measure
A simple DAX measure
A DAX measure
A more complex measure. It uses the SUMX iterator rather than the SUM aggregator function so it can calculate an expression rather than a column.
A DAX measure using virtual relationships.