DAX advanced

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Confident use of DAX context - You will learn to distinguish row context from filter context and use both deliberately in measures, so your Power BI calculations stop breaking due to hidden context mistakes.

Stronger model aggregations - You will master SUMX, AVERAGEX, COUNTX, MINX and MAXX to calculate data correctly across nested tables, relationships and different levels of detail in your data model.

Time analysis from scratch - You will build year-over-year, month-over-month and quarter-over-quarter comparisons on your own, using DAX time functions for dynamic trend and seasonality analysis.

Dynamic conditional measures - You will use IF, SWITCH, ISBLANK and logical conditions to create measures that react to user selections, filters and missing data without rebuilding reports by hand.

Faster, lighter reports - You will apply optimization techniques with CALCULATE, FILTER, ALL and REMOVEFILTERS to reduce calculation cost and improve Power BI model performance on large datasets.

Custom measures and helper tables - You will learn to create custom measures and dynamic helper tables with SUMMARIZE, ADDCOLUMNS, VALUES and variables, making complex business analysis easier to design and scale.

Better control of relationships - You will understand how table relationships affect DAX calculations, so you can design a cleaner data model and avoid errors caused by ambiguous or unintended filter behavior.

Effective formula debugging - You will test and troubleshoot measures with EVALUATE, SUMMARIZECOLUMNS and VALUES, helping you identify wrong results faster and improve the reliability of your reports.

Training programme

1. Introduction to advanced DAX

brief review of DAX basics:
- syntax and structure of formulas,
understanding context in DAX:
- row context vs. filter context,
- how DAX uses context in calculations,
the role of calculations in data models and Power BI reports.

2. Advanced Aggregation Functions and Context in DAX

aggregation functions:
- SUMX,
- AVERAGEX,
- COUNTX,
- MINX,
- MAXX,
use of aggregation functions in nested tables,
data aggregation in the context of a table and a row,
DAX in the context of tables and relationships:
- relationships between tables and calculations in DAX,
- use of aggregation with different levels of relationships.

3. Time functions in DAX

functions for analyzing data over time:
- SAMEPERIODLASTYEAR,
- PARALLELPERIOD,
- DATEADD,
- DATESINPERIOD,
- TODAY,
- YEAR,
year-over-year, month-over-month, quarter-over-quarter comparisons,
calculations based on periods and dynamic dates,
analysis of seasonality and trends in long-term reports,
application of time functions to create custom reports.

4. Advanced conditional and logical functions

functions:
- IF,
- SWITCH,
- IFERROR,
- ISBLANK,
- AND,
- OR,
- NOT,
application of logical conditions in measures,
conditional functions in dynamic reports,
creating dynamic measures depending on the changing context,
managing errors and empty data in reports and calculations.

5. Optimization of data models in Power BI

principles of performance optimization in DAX,
optimization of DAX queries using CALCULATE, FILTER, ALL, REMOVEFILTERS,
techniques for improving calculation performance in Power BI models,
the role of indexes, aggregates and measures in optimization,
minimization of computational costs in large data sets,
use of the EARLIER, FILTER functions for query optimization.

6. Advanced custom measures and helper tables

creating dynamic measures using the functions CALCULATE, FILTER, ALL, CROSSJOIN,
generating dynamic helper tables with the functions SUMMARIZE, ADDCOLUMNS, VALUES,
using variables in DAX to increase calculation performance,
working with helper tables and custom aggregations.

7. Time and context management in DAX

advanced time manipulation functions : PREVIOUSMONTH, NEXTMONTH, DATESYTD, DATESMTD, DATESQTD,
creating custom periods and dates (e.g. weeks, quarters),
dynamic calculations depending on the selected date range,
application of time formulas in financial and analytical reports.

8. Creating reports and dashboards with advanced measures

integration of advanced measures in Power BI reports,
use of measures to create interactive dashboards,
working with filters and slicers in reports,
optimization of report performance with large data sets.

9. Advanced debugging and testing techniques in DAX

tools for testing DAX formulas,
debugging measures and formulas using EVALUATE, SUMMARIZECOLUMNS, VALUES,
analysis of formula performance in Power BI,
solving problems related to incorrect data and calculations.

What are the prerequisites for participating in the training?

Power BI basics - You should be comfortable navigating Power BI and creating simple reports, using fields, visuals and filters, so you can focus on advanced DAX rather than the interface.

Basic DAX knowledge - You should know basic DAX syntax and be able to create simple measures or calculated columns, because the course starts with a brief recap rather than absolute basics.

Data model fundamentals - You should understand tables, relationships and the main parts of a data model, so it is easier for you to follow how context and filters affect DAX calculations.

Data analysis experience - You should have practical experience with data analysis and reporting, including dates, aggregations and period comparisons, because the training expands these areas in DAX.