Course details

Duration: 1-day

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

This course starts with data transformation strategies, exploring capabilities in the Power Query Editor, and data-cleansing practices. It looks at the Advanced Query Editor to view scripted M coding.

This course focuses on advanced DAX measures that include filtering conditions, with a deep dive into time intelligence measures. Like the M query language, DAX is a rich functional language that supports variables and expression references.

This course also looks at the creation of dynamic dashboards that incorporate a range of visualisations in Power BI Desktop, and the online custom visuals from the AppSource.

Prerequisites

My assumption is that you have either attended both the Power BI Introduction, and Power BI Intermediate level courses, or are already a proficient Power BI Desktop user.

Learning objectives

Lesson 1. The Query Editor

  • Grouping rows in tables
  • Split row by delimiter
  • AddDays to determine deadlines
  • Advanced query editor

Lesson 2. Fuzzy Matching Joins

  • Matching inconsistencies by percentage
  • Matching with transformation table

Lesson 3. Logical Column Functions

  • Using multiple conditions
  • Including FIND in functions
  • Logical functions IF, AND, OR
  • Using OR to handle font casing

Lesson 4. Editing DAX Measures

  • Making DAX easier to read
  • Add comments to a measure
  • Using quick measures

Lesson 5. The Anatomy of CALCULATE

  • Understanding CALCULATE filters
  • Add context to CALCULATE with FILTER
  • Using CALCULATE with a threshold

Lesson 6. The ALL Measure

  • Anatomy of ALL
  • Create an ALL measure
  • Using ALL as a filter
  • Use ALL for percentages

Lesson 7. DAX Iterators

  • Anatomy of iterators
  • A closer look with SUMX
  • Using RELATED in SUMX
  • Create a RANKX
  • RANKX with ALL

Lesson 8. Date and Time Functions

  • Overview of functions
  • Create a DATEDIFF function

Lesson 9. Time Intelligent Measures

  • Compare historical data
  • Create a DATEADD measure
  • Creating cumulative totals
  • Creating cumulative measures
  • Visualising cumulative totals

Lesson 10. Visualisations in-Depth

  • Utilising report themes
  • Create a heatmap
  • Comparing proportions
  • View trends with sparklines
  • Group numbers using lists
  • Group numbers using bins

Lesson 11. User Level Security

  • Setting up row level security
  • Testing user security