Writing Effective DAX in Power BI

Who is this course for?

Dax is the calculation language of Power BI and is the key to building powerful measures.  A good knowledge of DAX is essential for any reasonably complex Power BI set of reports. This course is for data analysts who use Power BI regularly in their work and need to use DAX well.

Learning Objectives

After the course, 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 – 3 days

Course Content

  • DAX Calculated Columns, Measures, Tables.  Implicit and Explicit measures. 
  • Aggregator Functions e.g. SUM() vs Iterator (X) functions e.g. SUMX()
  • Common DAX functions including: DIVIDE(), COUNTROWS(), IF(), SWITCH(), CALENDAR(), CALENDARAUTO(), SELECTCOLUMNS(), ADDCOLUMNS(), UNION(), DISTINCT(), TOPN(), ALL(), ALLSELECTED(), HASONEVALUE(), SELECTEDVALUE(), RELATED(), RELATEDTABLE(),GENERATESERIES() and FILTER()
  • 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()

Pre-requisites

Familiarity with data modelling in Power BI – for example by attending our Data Modeling course.