Power BI – Advanced DAX

Course Description

This advanced two-day course develops participants' knowledge of Data Analysis Expressions (DAX), enabling them to create sophisticated calculations, solve complex business reporting requirements and optimise Power BI data models. Building on existing DAX skills, the course explores advanced evaluation contexts, iterator functions, virtual tables, complex filtering, time intelligence and performance optimisation. Participants will also learn how to develop reusable calculations using calculation groups and DAX user-defined functions. Through practical exercises and real-world business scenarios, participants will develop the skills to create efficient, maintainable DAX solutions for advanced reporting and business analysis.
2 Days
Contact us for pricing
 

Prerequisites

Participants should have completed a Power BI DAX introductory course or possess equivalent practical experience. They should be confident creating DAX measures, using CALCULATE, working with row and filter context, and creating basic time-intelligence calculations.
Participants should also have a good understanding of Power BI data modelling, table relationships and the principles of a star schema.

Who Should Attend

This course is designed for experienced Power BI users, business intelligence developers, data analysts and reporting professionals who already use DAX and wish to extend their skills to more complex analytical requirements.
It is particularly suitable for professionals who develop and maintain Power BI semantic models, financial reports, management dashboards and operational reporting solutions.

Learning Objectives

Apply advanced row context, filter context and context transition techniques.
Develop complex calculations using iterator and table functions.
Create and manipulate virtual tables for business analysis.
Use advanced filtering techniques to control calculation behaviour.
Develop sophisticated time-intelligence measures and comparisons.
Create dynamic measures and interactive reporting calculations.
Use calculation groups and user-defined functions to improve reusability.
Identify and resolve DAX performance issues.
Test and troubleshoot complex expressions using DAX Query View.
Develop maintainable DAX solutions for advanced business reporting.

Day One – Advanced DAX Calculations and Analysis – Advanced DAX Concepts and Evaluation Context

Reviewing measures, calculated columns and evaluation contexts
Understanding context transition in greater depth
Advanced use of CALCULATE and CALCULATETABLE
Understanding expanded tables and relationship behaviour
Controlling filter propagation and relationship evaluation
Understanding the interaction between row context and filter context

Advanced Filtering Techniques

Using ALL, ALLEXCEPT, ALLSELECTED and REMOVEFILTERS
Applying KEEPFILTERS to modify filter behaviour
Working with USERELATIONSHIP and inactive relationships
Applying filters across tables using TREATAS
Combining multiple filtering conditions
Creating measures that respond intelligently to slicers and selections

Iterator Functions and Advanced Aggregations

Understanding how iterator functions evaluate expressions
Working with SUMX, AVERAGEX, COUNTX and MAXX
Creating weighted averages and conditional aggregations
Using RANKX and modern ranking approaches
Combining iterators with filtering and context transition
Avoiding common iterator and aggregation errors

Virtual Tables and Table Expressions

Understanding virtual tables and their uses
Creating table expressions with FILTER, VALUES and DISTINCT
Using ADDCOLUMNS and SELECTCOLUMNS
Summarising data with SUMMARIZE and SUMMARIZECOLUMNS
Building advanced calculations using virtual tables
Using TOPN for top-performing and bottom-performing categories
Developing customer, product and profitability analysis

Practical Workshop – Advanced Business Analysis

Customer and product rankings
Weighted averages and contribution analysis
Dynamic Top N reporting
Profitability and margin analysis
Complex calculations across related tables

Day Two – Advanced Time Intelligence, Reusability and Performance – Advanced Time Intelligence

Reviewing date tables and time-intelligence requirements
Using DATESBETWEEN, DATESINPERIOD and DATEADD
Developing rolling 3-, 6- and 12-month calculations
Creating complex period comparisons
Working with fiscal years and non-standard reporting periods
Comparing actual results with prior periods and budgets
Introduction to calendar-based time intelligence
Understanding approaches to rolling and cumulative calculations

Dynamic Measures and Interactive Analysis

Developing measures that respond to user selections
Using SELECTEDVALUE and HASONEVALUE
Applying disconnected tables to control calculations
Using field parameters for dynamic report analysis
Creating dynamic KPI measures
Developing flexible variance and performance reporting

Reusable DAX and Calculation Groups

Understanding calculation groups and calculation items
Creating reusable time-intelligence calculations
Applying calculation groups to multiple measures
Understanding precedence and calculation behaviour
Introduction to DAX user-defined functions
Creating parameterised, reusable DAX functions
Maintaining consistent calculation logic across reports

DAX Performance Optimisation and Troubleshooting

Understanding common causes of poor DAX performance
Recognising inefficient filters and iterator expressions
Using variables to improve readability and avoid repeated calculations
Understanding how data model design affects performance
Identifying expensive calculations
Using Power BI Performance Analyzer
Testing and debugging expressions with DAX Query View
Introduction to DAX Studio for detailed performance analysis
Using AI-assisted tools to explain and review complex DAX formulas

Advanced Practical Workshop

Developing complex financial or operational measures
Creating dynamic comparisons and performance indicators
Applying rolling-period analysis
Developing ranking and contribution calculations
Implementing reusable calculations and reporting logic
Testing and optimising DAX performance
Power BIMS Power BIAdvanced DAXDAXsemantic modelstime intelligencecalculation groups