Mastering DAX

An Introduction to DAX

This chapters provides a introduction to the DAX language. Students will learn the different use-cases of the DAX language.

• DAX Use Cases
• Understanding Calculated Columns and Measures
• Building Tables with DAX
• DAX as a Query Language
• DAX for Row Level Security
• DAX Patterns
• LAB: Creating Calculated Columns, Tables and Measures in DAX

Using DAX Query View

DAX Query View is a feature in Power BI that allows developers to write, run, and analyze DAX queries for deeper insights into their data models. In this module, you will learn how to use DAX Query View in Power BI Desktop and the Power BI Service, along with an introduction to writing DAX statements and exploring key functions.

• Using DAX Query View in Power BI Desktop
• Using DAX Query View in the Power BI Service
• Writing DAX Statements
• Important DAX Functions
• Using INFO. Information Functions
• LAB: Running and Profiling Queries in Power BI Desktop

DAX Expressions

When writing scalar expressions a good understanding of the DAX data types, operators and functions is needed.

• DAX Data Types
• Mathematical, Boolean and String Operators
• The Most Important Scalar Functions in DAX
• Conditional Functions
• Date and Time Functions
• LAB: Evaluating and Writing DAX Expressions

Creating DAX Measures

The CALCULATE function is probably the most used function in DAX when you are defining measures. To really understand how to use the CALCULATE function, you must have a good understanding of the concepts of row and filter contexts, which are also explained in the chapter.

• Row Context vs Filter Context
• Building Measures
• Aggregation Functions
• Defining Measure Tables
• Implicit vs Explicit Measures
• The CALCULATE Function
• Defining Table Scopes
• Using the FILTER, ALL, VALUES and DISTINCT Functions
• LAB: Using CALCULATE

Time Intelligence with DAX

Nearly all analysis happens against different time periods. DAX includes time intelligence functions that enable you to manipulate data using time periods, and then build and compare calculations over those periods. Common challenges such as year-to-date, growth calculations, moving averages etc. are covered in this module.

• Creating Calendar Tables using CALENDAR and CALENDARAUTO
• Working with Multiple Data Tables
• Marking Tables as Data Tables
• Using the DATEADD and PARALLELPERIOD Functions
• Computing YTD, QTD and MTD Totals
• Comparing Data over Time using DAX
• Calculating Rolling Totals
• Calculating Moving Averages
• Creating Time Intelligence Measures using Bravo
• LAB: Implementing Time Intelligence

Working with Calculation Groups

Often measures such as a year-to-date (YTD) calculation have to be repeated for multiple base measures. Calculation groups provide a template with which these repetitive measures can be easily created and maintained.

• The Need for Calculation Groups
• Creating Calculation Groups
• Format Strings in Calculation Groups
• Excluding Measures from Calculation Groups
• Combining Calculation Groups with Field Parameters for Dynamic UX
• LAB: Implementing a Time Intelligence Calculation Group

Measures: Iterators and Variables

When you need to iterate through data iterators such as SUMX become useful. This chapter discusses how to work with iterators, how to apply context transitions within iterators and how to use variables to increase readability, performance and expressive power

• The SUMX Aggregation Function
• Linking Tables with RELATED and RELATEDTABLE
• More Iterators: MAXX, RANKX, CONCATENATEX ...
• Using Variables to Increase Readability and Performance
• Context Transition and its Pitfalls
• LAB: Using Iterators in Measures

Inspecting and Controlling the Execution Context

Often a measure only makes sense in a certain context (e.g. only relevant at the month level, but not the year level). In this chapter DAX functions are covered which allow you to inspect the current context such that you can protect your measures. A typical use case are percentage of parent calculations, which are discussed in detail.

• HASONEVALUE
• ISFILTERED
• ISCROSSFILTERED
• ISINSCOPE
• SELECTEDVALUE
• ALLSELECTED
• Handling Hierarchies
• Calculating Ratios to Parent Subtotals
• Raising Errors
• Using Visual Calculations
• LAB: Inspecting Execution Context

Advanced DAX Constructs

In this last chapter some of the more advanced DAX topics are introduced.

• Context Modifiers USERELATIONSHIP, CROSSFILTER, ALLEXCEPT ...
• Expanded Tables
• TREATAS
• CALCULATETABLE
• LAB: Working with Expanded Tables