Call Us: +32 2 466 00 16
Email: info@u2u.be

# Mastering DAX

3 days

#### Upcoming Sessions

Date:

Format:

Location:

Date:

Format:

Location:

Date:

Format:

Location:

Date:

Format:

Interested in a private company training? Request it here.

#### 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

#### DAX Studio

A good tool can make the job a lot easier. And DAX Studio is a super handy tool for DAX developers, independent of whether you are using Power BI Desktop, Excel or Visual Studio in the end. This module explains why and how to use DAX Studio. But it also introduces the structure of DAX queries.

• Introducing DAX Studio
• Installing DAX Studio
• Writing DAX queries: EVALUATE, ORDER BY and START AT
• Constructing tables with FILTER, DISTINCT, ROW, SELECTCOLUMNS and SUMMARIZECOLUMNS
• Who's eating up the resources: Querying meta-data
• Performance tuning
• Profiling DAX queries
• LAB: Running and profiling queries in DAX Studio

#### DAX expressions

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

• DAX data types
• Getting started with constants
• Mathematical, boolean and string operators
• The most important scalar functions in DAX
• 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.

• Introduction to evaluation context
• The CALCULATE function and the row and filter context
• Aggregation functions
• Defining table scopes
• Using ALL and VALUES
• LAB: Using CALCULATE

#### Using CALCULATE for time intelligence

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
• Mark as Data Table
• Computing YTD/QTD and MTD totals
• Comparing data over time using DAX
• Calculating Rolling Totals
• Calculating Moving Averages
• 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 items
• LAB: Implementing a calculation group and calculation items

#### More on measures: Iterators

When you need to iterate through data iterators such as SUMX become useful. This chapter discusses how to work with iterators, and how to apply context transitions within iterators.

• SUMX aggregation function
• Linking to different tables with RELATED
• More iterators: RANKX, CONCATENATEX, ...
• Using variables in DAX
• Context transition
• 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.

• Why do we need the context?
• HASONEVALUE
• ISFILTERED
• ISCROSSFILTERED
• ISINSCOPE
• SELECTEDVALUE
• ALLSELECTED
• Handling hierarchies in DAX
• Calculate ratio's to a parent subtotals
• Raising errors in DAX
• LAB: Inspecting execution context

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

• CALCULATE modifiers USERELATIONSHIP, CROSSFILTER, ...
• Expanded tables
• TREATAS
• CALCULATETABLE
• Windowing functions: OFFSET, INDEX and WINDOW
• LAB: DAX Workshop

Power BI is a suite of business analytics tools to analyze data and share insights. DAX (short for Data Analysis Expressions) is a language used in Power BI and Analysis Services Tabular Models to create derived calculations such as year-to-date, percentage of total, etc. In this course you will learn how to use DAX to create measures and other derived calculations. You will also learn to use DAX as a query language.

This course is intended for people who have a background in Business Intelligence and have already built data models in Power BI or Analysis Services. U2U also offers a 3-day course Analyzing your data with Power BI for Business Users targeted at people without any skills to build a Tabular Model.