**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()