Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
xx Practical PowerPivot & DAX Formulas for Excel 2010
The incredible capabilities of PowerPivot can, optionally, be extended and refined
with DAX (Data Analysis Expressions). DAX is a new language for extracting business
intelligence within the familiar environment of Excel, and much of it is similar to already
well-known Excel functions. DAX is part of PowerPivot. It’s ideally suited for Excel
users and power-users, and requires no knowledge of the rest of Microsoft’s BI stack. If
the user is comfortable in Excel, they will be up and running with DAX functions and
formulas, and impressive pivot table and pivot chart results, within a few minutes.
This book is an introduction to PowerPivot for Excel, and the DAX language.
The first half concentrates on PowerPivot using the graphical interface, while the
second half is mostly DAX. The book is practical and hands-on, with a minimum of
concepts and theory. There are over 250 DAX formulas in the book—all downloadable
from www.mhprofessional.com/computingdownload. It’s aimed at Excel users, Excel
professionals, non-developers and developers, report designers, Excel power users,
decision makers, and all business intelligence professionals. The DAX formulas can
be used immediately in a corporate business intelligence environment and/or easily
customized for a specific business need.
Prerequisites
You will need Excel 2010, either 32-bit or 64-bit. You will also need PowerPivot for
Excel (32-bit or 64-bit to match your version of Excel 2010). PowerPivot for Excel
(either 32-bit or 64-bit) is a free download from www.powerpivot.com. You don’t
need SQL Server, or SharePoint, or PowerPivot for SharePoint to follow all the text,
screenshots, examples, and practicals in this book.
DAX Formulas Download
The source DAX for all the formulas in this book is available for download. You can
simply copy and paste into your DAX calculated columns or calculated measures to save
you some typing (even with AutoComplete, you still have to type the occasional closing
parenthesis and comma). You can download the source DAX formulas from www
.mhprofessional.com/computingdownload.
Sample Excel Data Download
Over 99 percent of the PowerPivot and DAX text, screenshots, examples, and practicals
in this book use the good old Northwind database. If you have SQL Server, you can
use the SQL Server version of Northwind. If you don’t have SQL Server, you can use
the pre-Access 2007 version of Northwind. As of this writing, both of these versions of
Northwind are still available for download from www.microsoft.com. Alternatively, you
Introduction xxi
might want to try an Excel version of Northwind. An Excel version can be downloaded
from this book’s Web site at www.mhprofessional.com/computingdownload. The
workbook is called Northwind.xlsx. There is, again as of this writing, a freely available
data feed version of Northwind. The URL for this data feed is given in Chapter 2,
when importing data from a data feed is discussed.
Full instructions of how to load SQL Server Northwind into PowerPivot are given
in Chapter 1. Full instructions of how to load the Access or Excel or data feed versions
of Northwind are given in Chapter 2. Chapter 2 also shows how to connect to the data
feed version.
Acronyms
C
BI Business Intelligence
C
BIDS SQL Server Business Intelligence Development Studio
C
DAX Data Analysis Expressions
C
DMX Data Mining Extensions
C
KPI Key Performance Indicator
C
MDX MultiDimensional Expressions
C
SQL Structured Query Language
C
SSAS SQL Server Analysis Services
C
SSIS SQL Server Integration Services
C
SSMS SQL Server Management Studio
C
SSRS SQL Server Reporting Services
C
XMLA XML for Analysis
Chapter Content
Part I: PowerPivot
Chapter 1—PowerPivot: Quick Start This is a short “quick start” chapter. It shows
just how easy it is to create business intelligence (BI) with PowerPivot for Excel.
Essentially a practical chapter, it does not contain much by way of theory or concepts—
these are introduced gradually throughout the rest of the book. The chapter shows how
to build quite a sophisticated pivot table in just a few minutes.
xxii Practical PowerPivot & DAX Formulas for Excel 2010
Chapter 2—PowerPivot: Overview Chapter 1 was a very quick introduction
to PowerPivot for Excel. It concentrated on importing data from SQL Server. This
chapter takes a more detailed look at data import from SQL Server. It also shows
how to import data from Access, Excel, and data feeds. You are shown relationships
between PowerPivot tables and how to set them up, if they are missing. DAX (Data
Analysis eXpressions) is used to create both calculated columns in PowerPivot tables
and measures in PowerPivot pivot tables in Excel. In addition, a measure is added
through the GUI, without the need for DAX. You get to construct a sophisticated pivot
table and a pivot chart linked to the pivot table. Various ways of filtering data in a pivot
report (pivot table or pivot chart) are introduced. This chapter consolidates and extends
the example in the previous chapter. It should give you a good overview of some of
the main features and capabilities of PowerPivot. Subsequent chapters build upon this
chapter—in particular, the next chapter is a full in-depth look at PowerPivot.
Chapter 3—PowerPivot: In-Depth This chapter goes a lot further than the previous
two introductory chapters. It’s a detailed and comprehensive guide to all of the ribbons:
Excel PowerPivot ribbon, PowerPivot Home ribbon, PowerPivot Design ribbon,
PowerPivot Linked Table ribbon. All of the ribbon groups and buttons are discussed,
some with step-by-step examples for you to follow. In the course of this chapter, you
will become familiar with most of the dialog boxes and menus that are relevant to
PowerPivot. This is essentially a grand tour of the PowerPivot graphical user interface.
Everything you are likely to meet in a PowerPivot window is covered. The DAX
language is introduced only briefly—Chapter 4 is specifically devoted to DAX. Later in
the book there are also many chapters providing a reference guide to DAX syntax. Pivot
table manipulation and customization is covered in Chapter 5.
Chapter 4—DAX: Overview DAX (Data Analysis eXpressions) is a new language
that makes its debut with PowerPivot for Excel. It is a language designed to use
formulas, which in turn are based on one or more DAX functions. Some of its functions
will look familiar to veteran Excel users, although there are differences between DAX
and Excel functions. These differences are elaborated in this chapter. You get to see
where and how to write DAX functions and formulas and there are a number of
examples to try. The all-important concept of filter context is discussed in detail. Other
topics include data types and operator precedence. Two of the most popular functions,
RELATED() and CALCULATE(), are covered in depth. The difference between
DAX calculated columns and DAX measures is explained. Hopefully, there is enough
DAX in this overview chapter to get you started. The second part of this book provides
a comprehensive reference, with examples, of all the DAX functions.
Introduction xxiii
Chapter 5—Pivot Tables and Pivot Charts: Overview This chapter is concerned
with looking at pivot reports—that is, pivot tables and pivot charts. The chapter is an
overview; a whole book could easily be written about pivot tables in Excel, and another
one just on pivot charts! The emphasis here is on creating pivot tables and charts. The
most important focus is on how to add data to a pivot report using the PowerPivot
Field List. But, once this is done, there are dozens of context menus, drop-down menus,
and quite a few ribbons with dozens of buttons. Some of these are explored in this
chapter, especially where they are particularly relevant to working with PowerPivot.
These menu options and buttons help you organize, control, and format your pivot
reports. By the end of the chapter, you should be in a position to create meaningful and
attractive pivot tables and charts.
Part II: DAX
Chapter 6—Fundamental Functions: Filter, Logical, and Miscellaneous
Functions This is a wide-ranging chapter on some of the fundamental DAX
functions. It includes syntax and examples for all of the Filter functions and all of the
Logical functions. In addition, it discusses some uncategorized functions—these are
often referred to as information or IS() functions. An example is ISBLANK(). There
is also one Text function, BLANK(), which has been added here because of its close
relationship to ISBLANK(). The Filter functions, in particular, are extremely powerful.
Among other things, they allow you to override the filter context that a user chooses
in a pivot table. They help to display classic BI values such as “percentage of parent” or
“percentage of all.” Some of the examples are shown as calculated columns, others as
measures. With measures, in particular, your results may differ from those shown in the
screenshots, as there a few different incarnations of Northwind—not always with the
same data!
Chapter 7—Aggregate Functions: Statistical Functions and SUM()/SUMX()
Functions The aggregate functions include the Statistical functions and both SUM()
and SUMXX() from the Math & Trig functions. Such functions often result in numeric
values. The functions can be used equally well as calculated columns or as measures.
If used as calculated columns, they are often added to the Values drop-zone in the
PowerPivot Field List and displayed in the central data area of a pivot table. They
are ideally suited for producing totals and subtotals. In this chapter, the alphabetical
order of the functions has not been observed completely. For example, AVERAGEX()
appears before AVERAGEA(), but after AVERAGE()—this is to highlight the
difference between a base column function, such as AVERAGE() and its table function
equivalent, AVERAGEX(). AVERAGEA(), another column function, then follows.
xxiv Practical PowerPivot & DAX Formulas for Excel 2010
In general, the X-functions, for instance, AVERAGEX(), accept a table argument. The
base functions, like AVERAGE(), accept a column argument, and operate on numeric
or date values. The A-functions, for example, AVERAGEA(), also accept a column
argument, but usually work on every value in a column, even text and blank values.
Chapter 8—Date & Time Functions 1/2: Date and Time Basic Functions For
the purposes of clarity, the Date & Time functions in DAX have been divided into
two chapters. This is the first of those two chapters and deals with the more basic date
and time functions. The next chapter is devoted to a group of Date & Time functions
that are often referred to as time intelligence functions. The basic functions are often
simpler and more forgiving. The time intelligence functions usually require that you
have put some thought into how your dates are stored in the PowerPivot model. The
emphasis in this chapter is on parsing, manipulating, and returning dates. The basic
functions generally return dates and times. The time intelligence functions generally
return measures or values associated with dates and times.
Chapter 9—Date & Time Functions 2/2: Time Intelligence Functions This is
the second chapter dealing with DAX Date & Time functions. The previous chapter
discusses some of the basic Date and Time functions. In this chapter, the emphasis is
on the time intelligence Date and Time functions. The time intelligence functions are
primarily used to navigate dates. In particular, they allow you to jump ahead or back in
time and retrieve relevant data. This is useful if you wish to compare your data across
or between time periods. Practical applications would include year-on-year changes.
They allow you to compare the present date, in your filter context, with past and future
dates. In addition, these functions can help you define ranges of dates or dates up to
a particular date. A practical application here might be year-to-date sales. The basic
functions generally return dates and times. The time intelligence functions generally
return measures or values associated with dates and times.
Chapter 10—Text Functions This chapter deals with text (or string) manipulation.
Most of the text functions are the same as their Excel function equivalents; however,
they accept text or column parameters rather than worksheet cells or ranges. Many
of these text functions return text, and as such are usually more suitable for creating
calculated columns in PowerPivot tables rather than measures—text values can’t be
added together to give subtotals and totals in a pivot table (unless you do a COUNT()).
Having said that, if the text value can be converted into a number (for example the string
“123”), then it may be used as an additive measure. Usually, PowerPivot will implicitly do
the conversion, but you can always do it explicitly with the VALUE() function. Please
note, the FORMAT() text function also operates against numbers as well as against text.
Introduction xxv
Chapter 11—Math & Trig Functions This is the final chapter dealing with DAX
functions and formulas. It deals with the Math & Trig functions. However, you won’t
find any trigonometric functions, as such—expect these to appear in a later version
of PowerPivot for Excel. Some of the functions, for example, EXP() and LN(), are
probably for specialized use only. However, many of the others are going to be very
popular—particularly, the eight functions devoted to rounding numbers. SUM() and
SUMX() are Math & Trig functions, although they have much in common with the
Statistical functions as well. They were both covered in an earlier chapter on aggregate
functions (that is, Statistical functions with SUM() and SUMX()), but are mentioned
here again for completeness. In any case, SUMX() is worth a second look, as it’s going
to be very useful in many BI situations.
Part III: PowerPivot and DAX Applied
Chapter 12—A Few Ideas: PowerPivot and DAX Solutions The real world is
the real world. Software, and books about software, can only give you “out-of-the-box”
solutions easily. Real-world solutions require a bit more work. This chapter presents
a few ideas for moving beyond “out-of-the-box” answers. It is all about implementing
PowerPivot and DAX to deal with common business problems. The three appendixes
that follow this chapter have a narrower focus: how to write SQL, MDX, and DMX
queries for assembling data in your PowerPivot model. In contrast, this chapter is more
concerned with using DAX and the PowerPivot GUI to provide solutions, once you
already have the data in place. That said, there is a little on importing data to address
the problem of working effectively with dates and dealing with self-joins.
Working with dates can lead to a number of problems—some of these are addressed
here. There are also a few “classic” BI solutions in this chapter—percentage of total and
subtotal, running totals, changes over time, moving averages, suppressing totals for non-
additive numbers, dealing with semi-additive numbers, customizing DAX formulas for
individual rows or columns, predefining filters, predefining Column Labels and Row
Labels with named sets, working without pivot tables, sharing your pivot reports with
others through SharePoint, and a few other things.
Part IV: Appendixes: Queries for PowerPivot
Appendix A—SQL Queries for PowerPivot This is a short appendix. It’s aimed
at those readers who need a brief introduction to SQL, with some basic syntax
examples. We also discuss the reasons for writing your own queries to import data into
PowerPivot, rather than simply importing complete tables. A few query fundamentals
are covered: filtering, sorting, grouping, and denormalizing data with joins and self-joins.
There are also examples of using a stored procedure and writing SQL against Excel.
xxvi Practical PowerPivot & DAX Formulas for Excel 2010
Appendix B—MDX Queries for PowerPivot MDX (MultiDimensional
eXpressions) is a very powerful query language for extracting data from cubes. While
PowerPivot can generate sophisticated MDX for you, you may want the total control
that writing your own MDX gives you. You can use MDX to import from either
an SSAS cube or a PowerPivot model that has been published to PowerPivot for
SharePoint. This appendix demonstrates some of the fundamentals of MDX. It also
shows how best to adapt your MDX for PowerPivot.
Appendix C—DMX Queries for PowerPivot You may have SSAS data mining
structures and data mining models. You normally query these objects using DMX
(Data Mining eXtensions), perhaps from SSMS (SQL Server Management Studio)
or from SSRS (SQL Server Reporting Services). Although this release of PowerPivot
supports the graphical design of SQL and MDX queries, it does not support the
graphical design of DMX queries. You can, however, write your own DMX. You do so
by connecting to an SSAS source and entering the DMX into the MDX Statement
area of the Specify a MDX Query dialog, in the Table Import Wizard. Another way
is to embed your DMX query within a SQL query and import the data returned from
the outer SQL query. To do that, you connect to a SQL Server source and enter the
SQL/DMX into the SQL Statement area of the Specify a SQL Query dialog of the
Table Import Wizard. Alternatively, you can query some data mining data from Excel
itself—for this you will need to download the data mining add-in for Excel (the data
mining results are shown in an Excel worksheet when you use the Table Tools/Analyze
ribbon, rather than the Data Mining ribbon), and you can then import or link into the
PowerPivot window from the Excel worksheet. Stand-alone DMX, or DMX embedded
in SQL, works for DMX Cases, Content, Prediction and other queries. This appendix
includes sample code for a few Cases, Content, and Prediction queries.
PowerPivot
Part I
This page intentionally left blank
PowerPivot: Quick Start
Chapter 1