Course details

Duration: One-day

Location: This course is run remotely online.

DAX, or Data Analysis Expressions, is the language that’s used to create formulas in Power BI Desktop, and they allow us to extend our data model. DAX gives us the ability to create additional information at runtime so that we can quickly and easily generate new information beyond what we have in our Power BI data model already.

If you are already familiar with data models in Power BI, then learning DAX is the logical next step. There are things that we can do with DAX that we can’t do as easily or at all without it. This course is designed to explore the capabilities of DAX within Power BI. This course focuses on modelling, the data model, not on reports. The only visualizations incorporated in this course are simple tables.

Prerequisite

This is not a beginner’s course in Power BI, my assumption is that you already know some things before attending. For example, you know how to connect to a data source, and once you have, you know how to add and remove columns, and change data types for example. If not, I recommend attending the Power BI Introduction course first.

Learning objectives

Lesson 1. Set Up a Data Model

  • Data modelling: the basics
  • Connect to a source file
  • Check relationships

Lesson 2. DAX Aggregate Functions

  • DAX filter context
  • Step-by-step measure calculation
  • Basic math and stats functions
  • Create a SUM measure
  • Understanding DAX syntax
  • Display a measure in a matrix
  • Aggregate COUNT measure
  • COUNTS and COUNTROWS measures
  • Check data with COUNTBLANK
  • Create a DISTINCTCOUNT measure

Lesson 3. Edit a Measure

  • Format a measure
  • Delete and edit a measure
  • Make DAX easier to read
  • Add comments to a measure
  • The quick measure option
  • Move a measure

Lesson 4. New Column Measures

  • Add a new column
  • Explicit vs. implicit measure
  • Create a measure with operators
  • The advantages to using DIVIDE

Lesson 5. Logical and Filter Measures

  • Logical functions IF, AND, and OR
  • Create a FIND new column
  • Create an IF new column
  • Using more than two conditions
  • Best practices
  • New columns vs. measures
  • Common function categories

Lesson 6. The SWITCH Function

  • Use SWITCH with TRUE

Lesson 7. Text Functions

  • Basic text functions
  • The CONCATENATE function
  • Use UPPER with LEFT
  • Use LEFT with S EARCH

Lesson 8. The CALCULATE Measure

  • Anatomy of CALCULATE
  • Use CALCULATE as a filter
  • Advanced filtering

Lesson 9. The ALL Measure

  • Anatomy of ALL
  • Create an ALL measure
  • Use ALL as a filter
  • Use ALL in percentage measures
  • Pass a table or column to ALL

Lesson 10. The RELATED Function

  • Create a RELATED function

Lesson 11. Iterators and Row Context

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

Lesson 12. Date and Time Functions

  • Basic date and time functions
  • Create a DATEDIFF function
  • The EOMONTH function

Lesson 13. Time Intelligent Measures

  • Compare historical monthly data
  • Create TOTALYTD/QTD/MTD measures
  • Create a DATEADD measure
  • Run cumulative totals
  • Use FILTER with ALLSELECTED and MAX
  • The DATESINPERIOD measure