Course details

Duration: One-day

Location: This course is run remotely online.

This course starts with data transformation strategies, exploring advanced capabilities of the Power Query Editor. Learn advanced data-cleansing practices, and advanced M formula queries.

Just like the M query language, DAX is a rich, functional language that supports variables and expression references. This course focuses on advanced DAX measures that include filtering conditions that override filters applied by the data model, and a deep dive into time intelligent measures.

The course also further covers the creation of dynamic dashboards and looks at working with both the visualizations in Power BI Desktop, as well as custom visualizations in the online AppSource.

Prerequisites

My assumption is that you have either attended both the Power BI introduction and intermediate level courses or are already a proficient Power BI user.

Learning objectives

Lesson 1. The Query Editor

  • Split by row
  • 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

  • Logical functions IF, AND, OR
  • Using multiple conditions
  • Including FIND in functions

Lesson 4. Editing DAX

  • Make 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 at 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 monthly data
  • Create a SAMEPERIODLASTYEAR
  • 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 bins
  • Setting up a histogram

Lesson 11. Comparing Variables

  • Visualising trendlines as KPI
  • Forecasting with trendlines
  • Creating a scatter plot
  • Creating dynamic labels
  • Customised visualisation tooltips
  • Export reports to SharePoint

Lesson 12. User Level Security

  • Setting up row level security
  • Testing user security