Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
344 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
You can return the same result by using INT(). However, to illustrate the difference
between INT() and TRUNC(), you may want to try the following two examples:
=INT(-7.2)
=TRUNC(-7.2)
INT() and TRUNC() are the same when applied to positive values—they behave
differently on negative values. The results are -8 and -7, respectively.
Part III
PowerPivot and
DAX Applied
This page intentionally left blank
A Few Ideas: PowerPivot
and DAX Solutions
Chapter 12
348 Practical PowerPivot & DAX Formulas for Excel 2010
T
he real world is the real world. Software, and books about software, can only
give you “out-of-the-box” solutions. 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,
of 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.
C
Key concepts Date and time solutions, totals and subtotals, contributions to
totals and subtotals, changes over time, non-additive and semi-additive numbers,
moving averages, predefining filters and sets, self-joins, working without pivot
tables, PowerPivot for SharePoint
C
Preparation Many of the examples in this chapter use a Northwind PowerPivot
model. Instructions on how to create this model from a SQL Server Northwind
source are in Chapter 1. Instructions on how to do so from an Access, Excel, or
data feed Northwind are in Chapter 2. One or two examples will use another data
source, namely the SQL Server sample database, ContosoRetailDW.
Do You Have a Problem on Dates?
Maybe you don’t always get the results you would like on dates—I often don’t! Most
PowerPivot models will include a date somewhere. Users often want to see the data by
year or quarter or month. Frequently, they will want to drill down or drill up on dates.
Dates are notoriously difficult to handle. This section includes a number of suggestions.
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 349
Parsing Dates
The Northwind sample has an OrderDate column in the Orders table. This has a data
type of date and is simply a date. Users like to slice, filter, and view years and quarters,
but these are not present as separate columns. Splitting a date into constituent parts is
called parsing a date. There are a number of ways of doing this. One, you can do it in
PowerPivot using DAX. Two, you can parse the date by writing a query for data import
into your model. Three, you can create the columns in the source data. Neither of these
is that difficult, but establishing fiscal years and quarters is more of a challenge. If you
do have fiscal periods, it might also be a good idea to concatenate FY, and also CY to
calendar periods, so dates in a pivot table are unambiguous. Let’s consider calendar
years and calendar quarters first—month names are the same for calendar and fiscal, but
month numbers may or may not be, depending upon your accounting conventions.
If you want to parse dates within the PowerPivot model, use DAX formulas. Here
are a few formulas to get you started. The first one is a calculated column to return the
calendar year—it’s called Year in some of the examples in this chapter. The second one
returns the month as a number—this column is called Month and is used by the next
three formulas, as well as later in the chapter. The month name (called MonthName
later in the chapter) is the result of the third example—it’s also easy to return the full
name, rather than an abbreviation. Formulas four and five are two alternative methods
of returning the calendar quarter (called Quarter).
=YEAR(Orders[OrderDate])
=MONTH(Orders[OrderDate])
=IF(Orders[Month]=1,"Jan",
IF(Orders[Month]=2,"Feb",
IF(Orders[Month]=3,"Mar",
IF(Orders[Month]=4,"Apr",
IF(Orders[Month]=5,"May",
IF(Orders[Month]=6,"Jun",
IF(Orders[Month]=7,"Jul",
IF(Orders[Month]=8,"Aug",
IF(Orders[Month]=9,"Sep",
IF(Orders[Month]=10,"Oct",
IF(Orders[Month]=11,"Nov",
"Dec")))))))))))
=IF(Orders[Month]<4,"Q1",
IF(Orders[Month]<7,"Q2",
IF(Orders[Month]<10,"Q3","Q4")))
=CONCATENATE("Q",ROUNDUP(Orders[Month]/3,0))
350 Practical PowerPivot & DAX Formulas for Excel 2010
Another way of extracting date parts is to write a query to import the table
containing the date. Here is the SQL to do so, against the SQL Server Northwind
database. It returns the date column itself, followed by the calendar year, the calendar
quarter, the month number, and the month name. Of course, you would want further
columns, such as OrderID and Freight. If you return both the month number and the
month name, it’s going to help you sort by, and show, month name in a slicer—more on
this shortly.
select OrderDate, 'CY ' + cast(DATEPART (yy,OrderDate) as char(4))
as [Calendar Year], 'Q' + cast(datepart(qq,OrderDate) as char(1))
as [Calendar Quarter], datepart(mm,OrderDate) as [Month Number],
datename(mm,OrderDate) as [Month Name]
from
Orders
It’s reasonably trivial to concatenate the year to the quarter—this is helpful if the
user only has quarters in Column Labels or Row Labels; it shows the year for each
quarter. You could also do the same for month number. However, if you add the year to
the month name, you may experience problems sorting by name in the pivot table.
You might want to experiment with the DAX and/or the SQL approach on the
Orders table. There is a third way of parsing dates. It’s even easier. However, it requires
that you have SSAS and BIDS. In an Analysis Services project, the dimension wizard
can generate all of this data (and more) for you. In addition, it can create fiscal as well as
calendar periods. There is more on this wizard in the next section on having a separate
table for dates.
Separate and Contiguous Date Table
It’s generally recommended that you create a separate table, just for holding dates. You
can then relate your other tables (such as Orders) to this separate table. When you
create a separate table, it’s usually called a date dimension or a time dimension. Having
such a table is standard practice in “traditional” BI. It confers a number of benefits.
One, it centralizes your dates and date manipulation—you don’t have to perform date
parsing across multiple tables containing dates. Two, it makes it easy to have contiguous
dates. If you look at the OrderDate column, some dates are missing. Having contiguous
dates helps the DAX time intelligence functions to return intuitive results—it doesn’t
matter that some of the dates are missing from the many-side table (in this case, that
would be Orders). This is particularly true when navigating dates and comparing dates
across time. Creating contiguous dates is fairly straightforward; you can use Excel
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 351
or BIDS. There is one drawback to having a separate time dimension. This occurs
when you have more than one date column in a table. You can only relate one of those
columns back to the one-side time dimension. The Orders table has OrderDate,
RequiredDate, and ShippedDate. If you went for the separate time dimension
approach, you would need three time dimensions. In this release, PowerPivot does not
support multiple relationships between tables. You can verify this (if you have SQL
Server AdventureWorksDW2008) by importing the DimDate and FactInternetSales
tables—two of the three relationships between those two tables are dropped during the
import. However, it’s simple to create two more time dimensions—select and copy all
of DimDate (press ctrl-a, and click Copy on the Home ribbon), and paste (click the
Paste button on the Home ribbon) into the PowerPivot model.
You can use Excel to create a separate time dimension from your dates. It’s
recommended that you make the dates contiguous. But where do you start and where
do you finish? A good place to start is January 1 of the first year for which you have
dates, and finish with December 31 of the last year for which you have dates. You might
choose Sort Oldest to Newest, then Sort Newest to Oldest, on the OrderDate column
to do this—or you could try MAX() and MIN(). To create a time dimension in Excel:
1. Enter a start date in a cell of an empty worksheet. With focus on that cell, click
the Fill button in the Editing group of the Excel Home ribbon. This opens a
drop-down menu, which is shown in Figure 12-1.
2. In the drop-down choose Series, which opens the Series dialog, shown in
Figure 12-2. In this dialog, turn on the option button for Columns and enter
a finish date (for instance, 12/31/2020, or 31/12/2020, depending upon your
regional settings) for the Stop value, before clicking OK.
3. Import this worksheet (or use a linked table) into your PowerPivot model.
4. Relate a date column in an existing PowerPivot table to the single date column
in this new table.
Figure 12-1 Fill drop-down menu
352 Practical PowerPivot & DAX Formulas for Excel 2010
If you have BIDS, there’s even more you can accomplish easily, including generating
fiscal time periods. To create a time dimension using BIDS (BIDS is beyond the scope
of this book—only an outline is given here, rather than detailed steps):
1. In BIDS, create a new SSAS project. Right-click Data Sources, in Solution
Explorer, in order to create a new data source. Point this to a relational database,
for example, SQL Server Northwind.
2. Right-click Dimensions and launch the Dimension Wizard. In the Select
Creation Method dialog, turn on the option button for Generate a Time Table in
the Data Source. The Select Creation Method dialog is shown in Figure 12-3.
3. In the Define Time Periods dialog, shown in Figure 12-4, specify the start and
finish dates, the first day of the week, and the time periods you require. Please
note, if you turn on Month, it will generate both month number and month name.
4. The next dialog, shown in Figure 12-5, is the Select Calendars dialog. In addition
to Regular Calendar, which means calendar dates, you might want Fiscal Calendar
and others.
5. When you are finished in the wizard, you should be looking at the dimension
designer for your new dimension. At the right-hand side, click on the link to
generate a data source view. This starts the Schema Generation Wizard. Step
through this wizard, and check that the table has been generated in the Schema
Generation Progress dialog, shown in Figure 12-6, before clicking Close. That’s
all! You can now import this new table into PowerPivot and relate your existing
date columns, in your existing tables, to the date column in the new table (it’s
called PK_Date by default and has a date data type). A sample generated table is
shown in Figure 12-7.
Figure 12-2 Series dialog
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 353
Perhaps a word of caution is necessary for those readers who have a BI background.
In “traditional” BI solutions, the time dimension often has an integer (Whole Number)
surrogate dimension key. The foreign keys that relate to this from fact tables are
also integers. The SQL Server sample database, AdventureWorksDW2008, uses an
intelligent key (for example, 20030701) rather than a surrogate key, but it’s still an
integer. DAX time intelligence functions require a date that’s a date data type. You
have to convert the primary and foreign keys to dates. Once that’s done, remove the
integer key relationship and relate the two tables on date. The DAX to convert an
intelligent integer key (in the form of 20030701) to a date is as follows:
=DATE(LEFT(DimDate[DateKey],4),MID(DimDate[DateKey],5,2),
RIGHT(DimDate[DateKey],2))
Figure 12-3 Select Creation Method dialog