Microsoft Office Tutorials and References

In Depth Information

Chapter 3

Introduction to DAX

Chapter 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

Introduction to DAX..................................... 47

Now that you have seen some examples of Microsoft SQL Server PowerPivot for Excel

worksheets, it is time to learn the fundamentals of PowerPivot expressions. PowerPivot

has its own syntax for defining calculation expressions. It is conceptually similar to an

Excel expression, but it has specific functions that allow you to create more advanced

calculation on data stored in multiple tables. The PowerPivot language is called Data

Analysis Expressions, but we always use the shorter DAX acronym.

In this chapter, you learn the basics of DAX and also discover how to use it to solve some

typical problems in business scenarios.

understanding Calculation in DAX

Just as it does in Excel, any calculation in DAX begins with the assignment operator. The

main difference is that DAX never uses cell coordinates like A1, C2, and so on. In DAX,

you always specify coordinates using column and table names. Moreover, DAX does not

support the concept of range as Excel does: to use DAX efficiently, you need to learn to

work with columns and tables.

Please note that in a DAX expression, you can get the value of a column only for a single row

or for the whole tableâ€”that is, you cannot get access to a specific row inside a table. To get a

range, you need to use DAX functions that filter a table, thus returning a subset of the rows of

the original table, corresponding to the needed range.

To express complex formulas, you need to learn the basics of DAX, which includes the syntax,

the different data types that DAX can handle, the basic operators, and how to refer to columns

and tables. In the next few sections, we are going to introduce these concepts.

DAX Syntax

A relatively simple way to understand how DAX syntax works is to start with an example.

Suppose that you have a PowerPivot table like the one shown in Figure 3-1. Two measures,

SalesAmount and TotalProductCost, are helpful here.