Microsoft Office Tutorials and References
In Depth Information
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.
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.