See the power of Data Analysis Expression (DAX) language in this free, 20-minute video by Alberto Ferrari, a renowned book author on the subject, consultant at sqlbi.com, and the many times winner of Best Overall Session at Microsoft TechEd and other conferences.

Although this is an older video, it remains very popular on our site because it shows the power of DAX very well even in the simpler form of the language! It is based on the version of DAX that has been introduced in PowerPivot and that was later integrated in SQL Server Analysis Services (SSAS) Tabular Mode.

One of the main reasons for the creation of the DAX language was the introduction of a new, simpler, columnar modelling paradigm in both PowerPivot for Excel, and in SSAS Tabular (0:41). Together, they leverage the incredibly fast xVelocity In-memory Analytics Engine: a powerful columnar database that is able to store billions of rows of data in a compact way, leading to ultra-fast, yet flexible, querying and calculating options.

In the first demo shown in this video, Alberto creates a simple data model (1:58), based on the AdventureWorks sample database (download here). Instead of querying it in just a few simplistic ways, we show you how to write a much more challenging formula: counting how many new and returning customers make a purchase every month (7:18). First, you learn how to count customers using DISTINCTCOUNT (8:17). Counting new customers requires the use of DAX expressions such as: CALCULATE, FILTER, and COUNTROWS (9:24).

PowerPivot for Excel is particularly useful for quick prototyping of your tabular expressions, before deploying them to SSAS for security, partitioning, and other reasons, which are explained in depth in this video, which is part of our Introduction to SQL BI online course.

After you learn how to count returning customers (13:46), Alberto shows you how to deploy our solution just created in PowerPivot to a SQL Server Analysis Services Tabular Model database (15:26).

As simple as it seems, this calculation required complex multidimensional modeling in previous versions of SSAS, leading to the need to modify the original data structures through some ETL code. Using DAX, it wasn’t even necessary to change the data model. Working on the simple, relational star-schema structure of the AdventureWorks data warehouse, we just wrote a slightly more complex DAX code that solved the problem without requiring a single step of ETL. Writing such DAX is not an easy task at first, it requires the attitude to learn, digest, and master the philosophy of DAX, which is quite different from the query language of previous versions of SSAS: MDX (Multidimensional Expressions).

Thinking in DAX represents a new attitude. If you would like to gain this skill, this video, first one in our new series focusing on DAX, shows you what could be accomplished without having to change the model of your data. Learning how to write DAX is a more advanced task, which we will cover in subsequent videos.

If you liked the video, please check out our courses by Alberto, Marco, and Chris, as well as their books on the subject.