Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
124 Practical PowerPivot & DAX Formulas for Excel 2010
The CALCULATE() function gives you total control over the way filters affect the
measures. You can use the function to honor an existing filter, you can ignore the
filter altogether, you can change the filter, you can honor the filter only in part, or
you can create a new filter context that is completely different from the filter context
implemented by the user in the pivot table GUI. Wow again!
In slightly simpler language, CALCULATE() lets you control how measures (added to
the Values drop-zone) change or don’t change as the user manipulates the pivot table.
Here’s a step-by-step example to get you started:
1. Create a new pivot table, or remove all entries from the PowerPivot Field List
drop-zones for an existing pivot table.
2. Add Category (from the Products table) to Row Labels. Add Year (from Orders)
to Slicers Horizontal. Add Country (from Customers) also to Slicers Horizontal.
Add Quantity (from Order Details) to Values—its default name should be Sum
of Quantity. The resulting pivot table is shown in Figure 4-9.
3. Notice the grand total is 51317. Use the slicer to show just 1996—the total is now
9581 (please be aware that there are different incarnations of Northwind with
differing years and sales figures—you may adjust the exercise to reflect your totals
and years). Remove the Year slice and slice on Austria from the Country slicer;
the total is now 5167. Then, try both 1996 and Austria; the result is 949. Then
remove the slices. You have just been changing the filter context of the measure
(an implicit measure based on a table column, Quantity) in the pivot table.
Figure 4-9 Pivot table example
Chapter 4: DAX: Overview 125
4. With the focus in the pivot table, click the New Measure button in the Measures
group on the PowerPivot ribbon. In the Measure Settings dialog, make sure the
Table name is Order Details; for Measure name, enter DAXMeasure; in Formula,
enter the following DAX formula; and click Check Formula. Your dialog should
look like Figure 4-10. Click OK. Your pivot table now resembles Figure 4-11.
=SUM('Order Details'[Quantity])
Figure 4-10 Measure Settings dialog
Figure 4-11 Pivot table with a measure based on a DAX formula
126 Practical PowerPivot & DAX Formulas for Excel 2010
5.
Check the overall total, then the totals for 1996, for Austria, and for 1996 and
Austria. Your new measure written in DAX is no different from the implicit measure.
6. Change the DAX formula. One way to do this is to click the Measure Settings
button, while the focus is in the pivot table. Alternatively, you can right-click the
measure in the field list and choose Edit Formula. Yet another method is to right-
click the measure in the Values drop-zone and choose Edit Measure. The new
syntax is as follows:
=CALCULATE(SUM('Order Details'[Quantity]))
7. Check the overall figure, 1996, Austria, and 1996/Austria again. There is no
change. The measure is still honoring the filter context.
8. Once more, change the formula. This time, extend the CALCULATE() function as
shown here:
=CALCULATE(SUM('Order Details'[Quantity]),ALL(Customers[Country]))
Try with no slicer (that is, no filter), just 1996 with all countries, just Austria with
all years, and just 1996 with Austria. The four results are shown in Figure 4-12
(No filter), Figure 4-13 (Year filter), Figure 4-14 (Country filter), and Figure 4-15
(Year and Country filter). These are the first of three sets of four results in this
exercise.
Figure 4-12 No filter context 1/3
Chapter 4: DAX: Overview 127
Figure 4-13 Year filter context 1/3
Figure 4-14 Country filter context 1/3
128 Practical PowerPivot & DAX Formulas for Excel 2010
T
his is the core of DAX. If you understand what’s happening, you understand DAX.
The ALL() function, in the preceding example, is acting as the filter or SetFilter
parameter of the CALCULATE() function. ALL() is overriding the current filter
context. The filter parameter is optional and you can have more than one. Here,
it’s telling the pivot table to ignore any filter on country (for example, Austria) that
the user may choose. It’s always going to show the figure for all countries—thanks
to the ALL() function. Why might this be very useful? There are a number of
important reasons. For example, you can have highly customized and flexible
measures that display exactly what you want. Or, you might want to control the
scope of the end user—so that, even if they add countries and filter on a particular
country, the figures they see are still for all countries. You are programmatically
overriding the user actions in the GUI. Or you might have measures based on this
measure, and those new measures require the data for all countries.
9. Let’s experiment. Change the formula as follows:
=CALCULATE(SUM('Order Details'[Quantity]),ALL(Orders[Year]))
Now as you filter, the Year slicer is totally ignored, but the Country filter is
operative again. Some of the possible results are shown in Figure 4-16 (No filter),
Figure 4-17 (Year filter), Figure 4-18 (Country filter), and Figure 4-19 (Year
and Country filter). Take some time to digest the figures and understand what is
happening.
Figure 4-15 Year and Country filter context 1/3
Chapter 4: DAX: Overview 129
Figure 4-17 Year filter context 2/3
Figure 4-16 No filter context 2/3
130 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 4-19 Year and Country filter context 2/3
Figure 4-18 Country filter context 2/3
Chapter 4: DAX: Overview 131
Figure 4-20 No filter context 3/3
10.
And here’s yet another change to the formula (some results are shown in Figure 4-20
(No filter), Figure 4-21 (Year filter), Figure 4-22 (Country filter), and Figure 4-23
(Year and Country filter)):
=CALCULATE(SUM('Order Details'[Quantity]),
ALL(Orders[Year]),ALL(Customers[Country]))
You have removed the filter context for both Year and Country. No matter
which years and/or countries are chosen, the totals remain the same. The ALL()
function is repeated for both slicers/filters. However, you can have many slicers.
You can also filter from Report Filter or from Column Labels or from Row
Filters. If you had ten possible filters, you would need to repeat the ALL()
function ten times. The next step shows a very convenient alternative.
11. Change the formula as follows:
=CALCULATE(SUM('Order Details'[Quantity]),
ALLEXCEPT('Order Details',Products[Category]))
Try slicing on 1996 and/or Austria again. The results are unchanged from the
previous step. The ALLEXCEPT() function is causing the measure to ignore all
filters on the Order Details table (including the Quantity) except any filter based
on product category.
132 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 4-22 Country filter context 3/3
Figure 4-21 Year filter context 3/3
Chapter 4: DAX: Overview 133
12. This final step is given with a little less guidance. Try each of the following five
formulas in turn and observe the totals in the pivot table as you slice/filter on
1996 and/or Austria:
=CALCULATE(SUM('Order Details'[Quantity]),
FILTER(Orders,Orders[Year]=1996))
=CALCULATE(SUM('Order Details'[Quantity]),Orders[Year]=1996)
=CALCULATE(SUM('Order Details'[Quantity]),Customers[Country]="Austria")
=CALCULATE(SUM('Order Details'[Quantity]),
Customers[Country]="Austria",Orders[Year]=1996)
=CALCULATE(SUM('Order Details'[Quantity]),
Customers[Country]="Austria" || Customers[Country]="Mexico",
Orders[Year]=1996)
The first and second examples superficially produce the same result until you slice
on 1997—the choice is yours (FILTER() returns a fixed table). The second example is
more efficient than the first as it is evaluated only for the cells in the current context;
FILTER() evaluates against the whole Orders table. The fifth example includes
Mexico as well as Austria.
The CALCULATE() function, in our examples, has been using the SUM() function
on the Quantity. You could write the SUM() function as a separate measure and base
CALCULATE() on this new measure. In that case, the SUM() function would be
Figure 4-23 Year and Country filter context 3/3