Introduction to DAX language (Data Analysis Expressions)

Objectives

To learn how to create effective DAX formulas to get the most out of your data.

Targeted audience

Professionals and technicians

Content

Fundamental Concepts

• Syntax rules
• Difference between row context and filter context
• Types of calculations: measures vs. columns

Review of Basic Functions Common to Excel

• Time functions: DATE, YEAR, MONTH, DAY, EOMONTH, EDATE
• Text manipulation functions: LEFT, RIGHT, MID, SEARCH, FIND, FORMAT, BLANK, SUBSTITUTE, LEN, FORMAT
• Information functions: ISBLANK, ISERROR, LOOKUPVALUE, CONTAINS
• Logical functions: IF, SWITCH, AND, OR, NOT

Utility Functions for Building Expressions

• Using variables in expressions with VAR and returning the result of a block of operations with RETURN
• Creation functions: DATATABLE and GENERATESERIES
• Table manipulation functions: ADDCOLUMNS, SELECTCOLUMNS, SUMMARIZE, GENERATE, UNION, ROW

Tables and Filtering

• Activating a filter with FILTER
• Disabling filters with ALL, ALLNOBLANKROW, and ALLEXCEPT
• Using DISTINCT and VALUES
• Calculation with a filter with CALCULATE and CALCULATETABLE
• Following relationships with RELATED and RELATEDTABLE
• Tests with ISFILTERED, ISCROSSFILTERED

Counting Functions

• Counting with COUNT, COUNTA, COUNTBLANK
• Counting rows with COUNTROWS
• Distinct count with DISTINCTCOUNT

Iterator Functions

• Common iterator functions: SUMX, COUNTX, MAXX, MINX, CONCATENATEX, etc.
• Nesting iterator functions
• Retrieving the value of external loop iterators with EARLIER and EARLIEST
• Determining field order with RANKX

Time Intelligence Functions

• Importance of the date table for time intelligence functions
• Creating a date table with CALENDAR, CALENDARAUTO
• Usage of a few families with DATESYTD, DATESMTD, DATESQTD, PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH, SAMEPERIODLASTYEAR

Parent/Child Hierarchy Functions

• Establishing the hierarchy with PATH
• Hierarchy length with PATHLENGTH
• Checking if a value is in the lineage with PATHCONTAINS
• Extracting elements from a hierarchy with PATHITEM and PATHITEMREVERSE