Course details

Duration: 1-day

Location: This course is run virtually online or face-to-face onsite.

DAX (Data Analysis Expressions) is a language used to create formulas in Power BI Desktop to extend our data model. DAX is not a programming language, it is code kept inside a formula which gives us the ability to create additional information at runtime, so that we can quickly and easily generate new information beyond the information that’s already stored in Power BI.

If you already know how to build data models in Power BI Desktop, learning DAX is a logical next step because there are things that we can do with DAX that we can’t do as easily, if at all, without it. This course is designed to explore the capabilities of DAX within Power BI Desktop.

Prerequisite

My assumption is you are already using Power BI and your only requirement is to learn about DAX functions and measures. If not, I recommend attending the Power BI Introduction or Intermediate course first.

Learning objectives

Lesson 1. Setting Up a Data Model

  • Data modelling: the basics
  • Importing source files
  • Data model relationships

Lesson 2. Aggregate DAX Functions

  • DAX filter context
  • Step-by-step measure calculation
  • Basic math and stats functions
  • Create a SUM measure
  • Understanding DAX syntax
  • Displaying measures in visuals
  • Aggregate COUNT measures
  • COUNTA and COUNTROWS measures
  • Checking data with COUNTBLANK
  • Using DISTINCTCOUNT

Lesson 3. Editing Measures

  • Formatting measures
  • Deleting and editing measures
  • Making DAX easier to read
  • Adding comments to measures
  • Using quick measures
  • Moving measures

Lesson 4. New DAX Columns

  • Adding new columns
  • Explicit vs. implicit measures
  • Using math operators in measures
  • Advantages of using DIVIDE

Lesson 5. New Column Functions In-Depth

  • Understanding logical functions
  • Using FIND functions
  • Using FIND in IF functions
  • Using more than two conditions
  • Handle font casing with OR
  • Best practices
  • New columns vs. measures
  • Common function categories

Lesson 6. Text Functions

  • Standard text functions
  • The CONCATENATE function
  • FORMAT: a useful text function
  • Using UPPER with LEFT
  • Using LEFT with SEARCH

Lesson 7. The CALCULATE Measure

  • Using CALCULATE as a filter
  • Advanced filtering
  • Understanding CALCULATE filters

Lesson 8. Adding Context to CALCULATE with FILTER

  • Using CALCULATE with a FILTER measure
  • Using CALCULATE with a threshold

Lesson 9. The ALL Measure

  • Anatomy of ALL
  • Creating ALL measures
  • Using ALL as a filter
  • Using ALL in percentage calculations
  • Pass a table or column to ALL

Lesson 10. Iterators and Row Context

  • Iterator “X” measure SUMX
  • Anatomy of iterators and row context
  • A closer look at SUMX

Lesson 11. Using RELATED in SUMX

  • The RELATED measure
  • The RELATED function

Lesson 12 The RANKX Measure

  • Creating a RANKX

Lesson 13. Date and Time Functions

  • Standard date and time functions
  • The DATEDIFF function
  • The EOMONTH function

Lesson 13. Time Intelligent Measures

  • Compare historical data
  • The DATEADD measure
  • Running cumulative totals
  • Visually view historical data