Course Outline – Effective DAX

Objectives of the Course

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
  • be more familiar with the most common DAX functions

Quick Recap – Data Modelling

We will review and recap the content of the data modelling course in a few hours

DAX Basics

Writing DAX in Power BI Desktop

DAX Outputs: calculated columns, measures, tables

Implicit Measures

Explicit measures.  How to create them (and hide the corresponding implicit measure). Why it is a good practice to use these rather than implicit measures

Aggregator Functions e.g. SUM()

Introduction to row context in calculated column

Iterator (X) functions e.g. SUMX(), MINX(), RANKX()  and FILTER()

Introduction to Implicit Filter Context in Measures

Languages basics: Arithmetic, Comparison & Logical Operators

DAX Vocabulary: Getting familiar with some common DAX functions that we’ll use in the course (and examples of use)

  • DIVIDE()
  • COUNTROWS
  • IF(), SWITCH()
  • CALENDAR(), CALENDARAUTO()
  • SELECTCOLUMNS(), ADDCOLUMNS()
  • UNION(), DISTINCT(), TOPN()
  • HASONEVALUE(), SELECTEDVALUE()
  • RELATED(), RELATEDTABLE()
  • GENERATESERIES()
  • FILTER()

Variables
VAR RETURN Syntax
Using several variables
Typical use cases and several examples

Context and Filtering

Implicit filter context (query context)

Explicit: filter Adding more filters

FILTER() 

Replacing implicit filter context:

  • CALCULATE() and CALCULATETABLE() function
  • ALL() and variations e.g. ALLSELECTED()
  • Examples of ALL() using several column and table parameters e.g. fact table,, dimension table, dimension table join column

Context transition

Common filter examples

  • Percentage of parent and percentage of total
  • Cumulative (rolling) values
  • Building time-intelligence functions e.g. year-to-date from scratch with CALCULATE() and ALL()

Time Intelligence

Why time intelligence is ubiquitous and important.

Using date (calendar) tables.  Marking as a date table.

The in-built time-intelligence function e.g. TOTALYTD()

More advanced cases e.g. moving average

Handling several date columns in the same fact table with USERELATIONSHIP()

Getting familiar with common date functions and their uses e.g. PREVIOUSMONTH(), EDATE(), PARALLELPERIOD(), DATEADD()

Summarising data with table functions

Overview of SUMMARIZE(), SUMMARIZECOLUMNS() and GROUPBY()

  • Similarities and differences
  • Which function to use in which case

Reasons for summarising data

  • Performance tables
  • Double aggregations: max of sum, netting calculations

Examples use cases

  • Get the number of days between current and previous events
  •  

Using SELECTCOLUMNS() and ADDCOLUMNS()

Joining and generating data

Using CROSSJOIN() for cartesian results

Using GENERATE() with FILTER()  to achieve join outside of the standard relationships

Example use-cases:

  • Creating a result table with a row for every date in a period (defined by start-date and end-date columns)
  • Two-column joins

Common DAX Patterns

How to recognise and use several common patterns including the

  • Parameter Table
  • Moving a column to another table with LOOKUPVALUE()