We continue our examination of DAX function / iterator pairs, in this Level of the Stairway to PowerPivot and DAX series, with the MAX() and MAXX() functions. MAX() and MAXX() support, respectively, the need to return the largest numeric value in a column,.and a requirement to return the largest numeric value obtained from the iterative evaluation of an expression for each row of a table.

As a part of our introduction, we will discuss ways in which we can employ MAX() and MAXX(); we'll look at each function in standalone scenarios, as well as in situations where we combine it with other functions, to meet the business requirements of clients, employers or peers in our own environments. During our exploration of the MAX() and MAXX() functions, we will:

• Discuss DAX function / iterator function pairs, in general;
• Examine the syntax involved in exploiting each;
• Undertake illustrative examples of the use of each function in practice exercises;
• Briefly discuss the results datasets we obtain in each of the practice examples.

As is always the case in my introductions to DAX functions throughout this Stairway series, we will examine the output we obtain, in this specific Level employing MAX() and MAXX() via calculated columns we construct within the PowerPivot window, in the practice session that follows our overview and explanation of the purpose and operation of each function. We will further create calculations that put both MAX() and MAXX() to work in PivotTable calculated fields, to further examine the behavior that we have observed in the calculated columns created in the PowerPivot window. Along the way, as appropriate, we will follow another path we have taken consistently within the Levels of this series: we will compare and contrast differences in use and behavior of the functions in calculated columns and calculated members in general, suggesting criteria to consider in choosing which of these calculation types to employ to meet the business requirements we encounter.

Preparation for the Levels of the Stairway to PowerPivot and DAX Series

To obtain the most benefit from the Levels of the Stairway to PowerPivot and DAX series, you will need to have enabled the Excel 2013 PowerPivot (embedded with Excel 2013 out-of-the-box) add-in on your PC. You can also use Microsoft Office Excel 2010 with the respective PowerPivot add-in - available for free at www.PowerPivot.com, as of this writing - to complete most steps of the levels of this Stairway, although naming conventions and screen appearances might differ a little from what you see in the prospective articles of this series. In many cases, you will also need to have installed SQL Server 2008 or above (including Analysis Services, for some Levels) and the respective relational and Analysis Services samples on your local machine, or have the client portions of each on your machine, with access to the rest on a server. To perform all steps of preparation for the Levels of Stairway to PowerPivot and DAX, see the installation section of the charter Level of this series, Level 1: Getting Started with PowerPivot and DAX.

NOTE: In this Level of the series, Excel 2013 with the PowerPivot add-in enabled, together with SQL Server 2012, is in use on a Windows 8.1 PC. The above installation instructions are still largely applicable for SQL Server, with the samples for SQL Server 2012 being obtainable from the same sources noted.

Preparation for the Practice Exercises in this Level

Once we've installed and configured Excel 2013, enabling the embedded PowerPivot add-in as part of the process, and have SQL Server 2008 (or later) and the samples noted above installed, we're ready to perform the practice exercises within this Level. Importing the sample database and taking other preparatory steps are critical to providing the environment required to successfully complete this Level, and should be performed before beginning.

Because we will work with different data sources throughout this series, the steps of environment preparation will be repeated in most Levels. I do this primarily to make each Level a freestanding session that can be completed independently of other Levels, so that you can focus upon gaining exposure to the specific skills required to meet your own immediate requirements efficiently, with all steps we undertake "in one place." Where appropriate, references to functions, procedures and other details will be supplied, but the objective of each Level is to focus upon one or more specific DAX functions and / or PowerPivot / PivotTable procedures to satisfy needs that mirror requirements we may encounter in our own business environments.

Let's open PowerPivot and import our data by taking the steps below. This will put us in position to begin learning the material in this Level.

1. From the Start menu, select Microsoft Excel, and begin with a blank workbook.
2. Above the Excel ribbon, from within the Home tab of a new worksheet, click the PowerPivot tab, as shown.

Illustration 1: Click the PowerPivot Tab within a New Worksheet…

The PowerPivot ribbon appears.

3. Click the Manage launch button, appearing at the left of the newly appearing PowerPivot toolbar, as shown.

Illustration 2: The Manage Launch Button on the PowerPivot Ribbon

The PowerPivot window, containing its own ribbon, opens atop the existing Excel spreadsheet, assuming the name of the workbook as its own name, as depicted.

Illustration 3: The PowerPivot Window, Associated with the Workbook (Here, Book1.xlsx), Appears

As we noted in Level 1: Getting Started with PowerPivot and DAX, it is in the PowerPivot window that we load and prepare the data with which we will be working (or will continue working, if data is already added to the workbook). We will typically build a relational model here (even when we import other-than-relational sources, such as Analysis Services data, text files, .RSS feeds, and so forth). As we also noted in Level 1, the PowerPivot window displays the tables on individual, tabbed sheets. This is the central place where we import tables, create relationships, maintain column data types and formats, and view the data that underlies our data model, among other actions.

Next, we'll designate a source from which to import data.

4. On the Home tab of the PowerPivot Window, click the downward-pointing selector arrow on the right side of the From Database button, within the Get External Data group on the ribbon.
5. Select From SQL Server on the cascading menu that appears next, as shown.

Illustration 4: Select "From SQL Server" in the Get External Data Dropdown Chain

The Table Import Wizard dialog opens next.

6. In the top input box, titled Friendly connection name, type (or copy and paste) the following (or another appropriate label):

AdventureWorksDW2012

7. Click the selector (downward pointing arrow) on the right side of the box titled Server name.

PowerPivot begins a scan of the machine to detect, and return to the selector, the available server choices.

8. Select the appropriate server for your local environment, or type in the server name / "localhost," as appropriate.
9. Enter the authentication as required for the local environment (ideally selecting Use Windows Authentication).
10. Select AdventureWorksDW2012 (or your appropriately versioned equivalent) using the dropdown selector to the right of the box titled, Database name, at the bottom of the dialog.

The Table Import Wizard dialog, with our input, appears similar to that depicted below.

Illustration 5: The Table Import Wizard Dialog, with Our Input

11. Click the Test Connection button underneath the Database name selector.

A message box appears, indicating that the test connection has succeeded.

Illustration 6: "Test Connection Succeeded"

12. Click OK to dismiss the message box.
13. Click the Next button at the bottom of the Table Import Wizard dialog.
14. On the dialog that appears next, labeled Choose How to Import the Data, leave the radio button at its default of Select from a list of tables and views to choose the data to import, as shown.

Illustration 7: Choose "Select from a list of tables…" Option for Data Import

15. Click the Next button.

PowerPivot loads the tables and views from the AdventureWorksDW2012 database into the Select Tables and Views page that appears next.

16. Select each of the tables listed in Table 1 by clicking the checkbox to the immediate left of the respective table listing in the Source Table column of the dialog, and change its name, via the Friendly Name in the dialog.

Table 1: "Select Table and Views" Dialog Input

The Select Tables and Views dialog appears, as partially shown, with the tables listed selected and designated with their new names.

Illustration 8: Selecting Tables (Partial View)…

The idea in these immediate steps, once again, is to import enough information into our model to allow us to do some illustrative analysis surrounding the business activities conducted by our hypothetical client, the Adventure Works Cycles organization.

17. Click the Finish button.

The import process runs, and then we see a "Success" message, complete with a Details list that indicates population by our choices.

Illustration 9: Success is Indicated, Along with a List of Tables Imported

18. Click Close.

The Table Import Wizard is dismissed, and we arrive at the PowerPivot window once again, where we see the imported data as partially depicted.

Illustration 10: Imported Data, with a Tab for Each Table (Partial View)

Note that a tab for each imported table has been created in the PowerPivot window (the tabs appear in the bottom left of the window, as seen above). What we are now seeing are not Excel tables / spreadsheets, but a view of the efficiently compressed columnar database that PowerPivot uses to store imported tables in memory. For more detailed information about the PowerPivot window itself, please see Level 1: Getting Started with PowerPivot and DAX.

19. Click File, to the immediate left of the Home tab.
20. Select Save As from the dropdown menu that appears, as shown.

Illustration 11: Select Save As from the Selector within the PowerPivot Window…

21. Save the worksheet as ST_DAX09-1.xlsx, placing it in a convenient location.
22. Leave the PowerPivot Window open for the practice session below.

We are ready, at this stage, to get some hands-on exposure to another couple of DAX functions, MAX() and MAXX(). We will also revisit functions that we introduced in previous Levels, while continuing to build experience with the general use of PowerPivot for Excel to meet business requirements, in the sections that follow.

The MAX() Function

Introduction

According to the Microsoft Developer Network (MSDN), the DAX MAX() function "returns the largest numeric value in a column." MAX() is a member of the Statistical function group, as displayed in the formula bar for PowerPivot and PowerPivot PivotTables, many of the members of which are identical to functions in Excel. The function, like many others, is often used in combination with other DAX functions.

Many of us have worked with the MAX() function in standard Excel, where, like SUM(), it is a relative "cornerstone" function. And, while anytime we drag a field into a PivotTable (either a "classic" PivotTable or a PowerPivot PivotTable) Values drop zone the default summary that is applied is Sum, we can choose from other treatments, including Max, as we can verify by right clicking upon the newly created sum of a value we have dropped into the Values drop zone, and then selecting Summarize By (whereby we can change the type of aggregation of a given Value). In the example below, we note the default behavior of the Sum option being checked as being in force, but that we can modify the aggregation behavior by selecting one of the other options – including Max - appearing below Sum on the cascading menu.

Illustration 12: Sum is the Default Treatment, but Max is among the Other Options…

MAX() simply returns the largest numeric value in a column containing numbers. The cells must contain either a numeric or date value, or MAX() simply ignores it in coming to the value that it delivers. If the column contains no numbers or dates, MAX() returns a blank. The MAX() function does not take a DAX expression as an argument.

When we employ MAX() across date values, the returned result is the last date within the range selected. (In the case of a selection of a single date, the MAX() expression, somewhat obviously, returns just that date.) When cells containing a value zero (0) exist inside columns specified within the MAX() function – as opposed to cells that are blank (which are not considered at all) – those cells are included in the determination of the maximum delivered for the column.

It is significant to note that the MAX() function calculates the maximum of all values (with the above noted exceptions) in a specified column. If a need exists to tabulate the maximum among a number of rows containing a specific numeric value, or an expression evaluating to a specific numeric value, we can do so via the MAXX() function (discussed in its own section of this Level).

We will explore the syntax for the MAX() function after a brief discussion in the next sections. We will then gain some hands-on exposure to its use, within practice examples constructed to support hypothetical business needs, in the Practice section later in this Level. This will allow us to activate what we explore in the Discussion and Syntax sections.

Discussion

To restate our initial explanation of its operation, the MAX() function returns the maximum of the date or numeric values within a specified column. (We can specify columns containing other data types, but, again, only rows with date or numeric values are taken into consideration by MAX().) The output of MAX() is a decimal number representing said maximum, or the maximum date if applicable, and the function can be used with equal utility within PowerPivot calculated columns or PivotTable measures. MAX() is often employed within other DAX functions.

NOTE: As we address in the section below, the MAXX() function allows us to extend the operation of MAX(), and take, as an argument, an expression that is, in turn, evaluated over a table / table expression. This enables us to perform independent calculations, and to subsequently take a maximum of the rows containing either a number / date or for which the stipulated expression evaluates to a number / date.

Syntax

Syntactically, in using the MAX() function to return the maximum, we specify the column of values we wish to be evaluated within the parentheses to the right of the MAX keyword. As an illustration, we'll cite the use of MAX() within the Reseller Sales tab of the PowerPivot window which we have established in our earlier preparation. Let's say we employ MAX(), via a calculated column, in the following manner to count the rows containing numbers in the ProductStandardCost column:

=MAX('Reseller Sales'[ProductStandardCost])

Were we to examine the new calculated column on the tab, we would see something like this (I've inserted the calculated column in the tab to be adjacent to the ProductStandardCost column, from which it is derived):

Illustration 13: Calculated Column Using MAX() on the Reseller Sales Tab (Partial View)

Reaching beyond the partial view we see above, and extending to all rows in the column, the calculation returns the desired outcome, based upon a maximum value contained in all rows in the ProductStandardCost column containing eligible values.

The MAXX() Function

Introduction

According to the Microsoft Developer Network (MSDN), the DAX MAXX() function "evaluates an expression for each row of a table and returns the largest numeric value." MAXX() takes two arguments, the first of which must be a table, or any expression that returns a table. The second argument is the column or expression that is searched by MAXX().

This enables us, of course, to perform calculations and then take the maximum returned calculated values. Like MAX(), MAXX() is an "aggregate" member of the Statistics function group, as displayed in the formula bar for PowerPivot and PowerPivot PivotTables, many of whose members are identical to functions in Excel. MAXX(), like other iterator ("X") functions, and by its very nature, lends itself to use in combination with other DAX functions.

As we've noted in other Levels within the Function / Iterator Function Pairs subseries of the Stairway to PowerPivot and DAX series, iterator functions typically contain a table expression (the first parameter specified within the function), for which a calculation, based upon an expression specified by a second parameter, is performed iteratively for each row of the table. The ultimate result is generated by the application of the respective aggregation function (including SUM, AVERAGE, COUNT, MAX, and MIN) to the dataset returned by the first and second expressions. We expose the behavior of other DAX "X" functions in independent examinations of each of the respective function / x-function pairs published in parallel to this review of the MAX() and MAXX() functions.

In cases where there are no "qualified" (meaning "containing number / date values") rows for which to calculate a maximum, MAXX() returns a blank. If the column / expression under evaluation contains a blank cell, MAXX() returns an empty column. With regard to nonnumeric values, the MAXX() function ignores any empty cells, text or logical values included within the specified column. MAXX() returns a zero (0) for expressions that do not evaluate to a number; only numbers or dates are considered in the "maximum" action.

NOTE: The MAXA() function supports the inclusion of non-numeric / non-date values. To gain exposure to the details involved with performing such counts, see the associated Level in this series.

MAXX() is useful anytime we want to filter a table we specify, based upon criteria we specify in a second expression, and then return a maximum of all the "qualified" values in the resulting column. If we do not need to apply qualifying criteria, we use the DAX MAX() function, which, like SUM(), is similar to the Excel function of the same name, except that it references a column, versus a specified range.

We will briefly discuss the MAXX() function in the section that follows, after which we will explore MAXX() from a syntax perspective. We will then activate what we have explored in the Discussion and Syntax sections, through hands-on exposure to its use, within practice examples constructed to support hypothetical business needs.

Discussion

As we have noted, the MAXX() function iterates through a specified table expression – the function's first parameter. It then employs the second parameter, a column (or an expression that delivers scalar output) for which we seek to generate a maximum, performing the expression for each row, and then all the respective date / number results obtained.

Let's look at some syntax illustrations to further clarify the operation of MAXX().

Syntax

Syntactically, in using the MAXX() function to evaluate expressions for each row of a table, and then tabulate the resulting set of values and calculate its maximum decimal number, we take a table expression (first argument of the function), and the expression (second parameter of the function) that we seek to apply to each row of the table (specified in the first argument), within parentheses, to the right of the MAXX keyword. The general syntax is shown in the following string:

MAXX(<table>,<expression>)

Putting MAXX(), or any of the "X iterator" functions, for that matter, to work is intuitive, once we grasp the purpose. When using the function to return the maximum of the results of the expression ("<expression>" in the syntax string above) upon each of the rows of the specified table ("<table>" in the syntax string above), we simply enclose the table and expression to the right of the MAXX keyword, as we have noted. The MAX function is then applied to the values returned for the respective rows, resulting in a single value that represents a count of those values.

As an example, let's get a feel for the general use of MAXX(), again within the Reseller Sales tab of the PowerPivot window, where we have performed the imports described in the Preparation section above. Let's say we want to put MAXX() to use in a scenario where we wish to do something along the lines of this:

=MAXX('Reseller Sales', 'Reseller Sales'[SalesAmount] + 'Reseller Sales'[TaxAmt])

This formula uses an expression as the second argument to calculate the total amount of sales and taxes for each order in the Reseller Sales table. The maximum of this calculation is them taken, and $30,125.11 returned.

A partial view of the results of this calculation appears as shown, alongside a simple calculation of "Sales plus Tax," which is sorted largest to smallest, and shows $30,125.11 as the largest value, confirming the value returned by the MAXX() function.

Illustration 14: MAXX() Results: Maximum of "Sales Plus Tax" Value for All Reseller Sales Table Rows (Partial View)

In this simple example, we can see how MAXX() works, allowing us to take, as an argument, an expression that is, in turn, evaluated for each row in a table (in this case, Reseller Sales) . We'll see a more sophisticated example in the Practice section below, where we see a maximum derived from a table subset of one of our imported tables.

We'll get some hands-on practice with MAX() and MAXX() in the sections that follow.

Practice

Let's next reinforce our understanding of the basics by putting the MAX() and MAXX() functions to work within the definition of some calculations. We'll do this as we have in the other Levels of the Stairway to PowerPivot and DAX series, first via calculated columns within tabs of the PowerPivot window, and then via calculated fields within a PivotTable of a worksheet within the associated Excel workbook.

Otherwise in keeping with our approach in other Levels of the series, we will examine the use of each of the functions in combination, where useful, with functions we introduce in other Levels. The intent, as in all the practice sessions we undertake together within this series, is to demonstrate the operation of the functions we examine in a straightforward, memorable manner. We will turn to the Excel worksheet we prepared earlier as a platform from which to construct and execute the DAX we examine, and to view the results we obtain.

PowerPivot Calculated Columns

We'll begin with a straightforward example: Let's say that we wish to return the birth date of the youngest customers on record in the Adventure Works DW 2012 data we have imported into our PowerPivot model. To do so, we will return the most recent – or "maximum" – birth data contained within the Customer data tab.

To this end, we will take the following steps:

1. Return to the worksheet we created in the Preparation for the Practice Exercises in this Level section above, ensuring that the PowerPivot Window is open, where we left it earlier.
2. Click the Customer tab.

Illustration 15: Click the Customer Tab…

3. Click the heading of the column labeled "MaritalStatus," which appears to the immediate right of the BirthDate column on the Customer tab, as shown.

Illustration 16: Click the Heading of the Column Labeled "MaritalStatus"

4. Right-click the column label for the MaritalStatus column.
5. Select Insert Column from the context menu that appears.

Illustration 17: Click Insert Column on the Context Menu…

6. A new calculated column, labelled CalculatedColumn1, appears.

Illustration 18: A New Calculated Column Appears…

Select the function button ("fx") to the left of the formula bar next.

Illustration 19: Select the Function (fx) Button…

The Insert Function dialog appears.

7. Click the downward pointing arrow on the selector appearing atop the dialog under the Select a category label.

The following DAX function categories appear:

• All
• Date & Time
• Math & Trig
• Statistical
• Text
• Logical
• Filter
• Information
• Parent-Child

8. Select the Statistical category, on the Select a category dropdown, as depicted.

Illustration 20: Insert Function Dialog with Expanded Category Selector

9. In the Select a function list that populates for the Statistical category, select the MAX() function, as shown.

Illustration 21: Select the MAX() Function

10. Click OK to confirm the selection and dismiss the Insert Function dialog.

The MAX keyword, proceeded by the "=" operator and followed by a left parenthesis ( "(" ), appears in the formula bar, with a function usage tooltip appearing just underneath the function.

Illustration 22: The Beginning of A Calculation Using MAX(), along with Syntax Tooltip, Appears

We will first create a simple calculation that generates a maximum of the entire Birth Date column of the Customer tab, which we will name "LatestBirthDate."

11. Following the left parenthesis inserted by our selection of MAX, begin to type in the word "Customer."
12. Scroll down and double-click / select the Customer[BirthDate] column within the dropdown list.

Illustration 23: Select the Customer[BirthDate] Column

13. Once the selector is dismissed, and =MAX(Customer[BirthDate] appears in the formula bar, type a right parenthesis ( ")" ) at the current end of the syntax, to close the MAX() function.

The syntax, at this point, should be as shown here:

=MAX(Customer[BirthDate])

14. Once the syntax shown above appears in the formula bar, press the ENTER key.

The new calculated column is populated, in every row, by the maximum of all values in the BirthDate column of the Customer tab. Let's next rename the column to its intended title.

15. Right-click the new column header label (currently at its default of "CalculatedColumn1"), and select Rename Column from the cascading menu that appears.

Illustration 24: Renaming the Column to its Intended Name

16. Name the newly added column "LatestBirthDate" to provide a new, distinct label.

The newly-named column, with updated formula results, appears as partially depicted below.

Illustration 25: The Results Appear in Each Row of the New Column (Partial View)

Thus we see how we can employ MAX() to deliver a simple "most recent date" within the PowerPivot window. We can easily verify the accuracy of the calculation by taking the following steps:

17. Right-click the downward-pointing selector button on the right side of the column label for the BirthDate column.
18. Select Sort Newest to Oldest from the selector dropdown that appears.

Illustration 26: Click Sort Newest to Oldest on the BirthDate Column Label Selector

We note that two instances of "12/26/1990" rise to the top as "newest" dates in the BirthDate column, verifying the results delivered by the LatestBirthDate calculation, as depicted in the excerpt of returned rows pictured.

Illustration 27: Results Verified: "Latest" Birth Dates Equal Those of LatestBirthDate Calculation

Next we'll take a look at the same operation, in a similar scenario, using MAXX().

MAXX() the" X-function" version of MAX(), is also a Statistical function. As we noted, in Level 6: Function / Iterator Function Pairs: The DAX SUM() and SUMX() Functions, was the case with the SUM() function, the MAX() function, like other functions in both Excel and in DAX, is limited to the use of a single column as an argument. The X-function counterparts (this time, MAXX() being the example) to several such "standard" functions (the respective example being MAX(), in the present case) enable us to construct arguments with multiple columns.

Let's look at an example that shows how we can use MAXX() to determine the largest numeric value based upon an expression applied to each row of a table.

In this example, we seek to obtain a maximum value for those Products listed in the Product table that belong to the Product Subcategory group (the membership of which is defined within the related Product Subcategory table) with the English name of "Tires and Tubes." Our objective will be to return the largest Standard Cost value appearing on the Product tab for those items meeting this criteria.

To meet the requirement, we'll take the following steps from the Product tab:

19. Click the top of the column labeled "WeightUnitMeasureCode," which appears to the immediate right of the ProductSubcategoryKey column, as shown.

Illustration 28: Click the Top of the Column Labeled "WeightUnitMeasureCode"

20. Right-click the column label for the WeightUnitMeasureCode column.
21. Select Insert Column from the context menu that appears, as we did in the previous exercise.

Illustration 29: Click Insert Column on the Context Menu, as Before…

A new calculated column, labelled CalculatedColumn1, appears, as we noted in earlier steps.

22. Select the function button ("fx") to the left of the formula bar next, as we did earlier.

The Insert Function dialog appears, once more.

23. Click the downward pointing arrow, once again, on the selector appearing atop the dialog under the Select a category label.
24. Select the Statistical category.
25. In the Select a function list that populates for the Statistical category, select the MAXX() function, as shown in Illustration 30.

Illustration 30: Select the MAXX() Function…

26. Click OK to confirm the selection and dismiss the Insert Function dialog, as we did earlier.

The MAXX keyword, proceeded by the "=" operator and followed by a left parenthesis ( "(" ), appears in the formula bar, with a function usage tooltip appearing just underneath the function, as we noted in working with the MAX() function earlier.

Illustration 31: The Beginning of Another Function, This Time MAXX(), Along with Tooltip…

We will continue with the creation of the calculation we have described; this time, we will generate "Std-Costed T & T MAX." Our new calculation is, as we have stated, intended to return the largest numeric value that results from evaluating an expression – in this case, the Standard Cost of those products belonging to the "Tires and Tubes" group within the related Product Subcategory table - for each row in the Product tab. So we are creating a calculation that returns the largest-valued row(s) of a column, with the rows filtered for criteria that exists within another column in another table, which we will specify in the appropriate argument of the function, as we shall see.

27. Following the left parenthesis inserted by our selection of MAXX, begin to type in the word "FILTER."

NOTE: For a detailed explanation of the operation of the DAX FILTER() function, see Level 2: The DAX COUNTROWS() and FILTER() Functions and More About Calculated Columns and Measures.

28. Select the FILTER function within the dropdown list by double-clicking the function, as depicted in Illustration 32.

Illustration 32: Select the FILTER Function…

29. Once the selector is dismissed, and =MAXX(FILTER( appears, begin to type in the word "Product."
30. Scroll down and double-click the Product table within the dropdown list, as shown.

Illustration 33: Select the Product Table…

31. Once the selector is dismissed, and =MAXX(FILTER(Product appears in the formula bar, type a comma ( , ) after "Product".

The syntax in the function bar, at this point, should be as shown here:

=MAXX(FILTER(Product,

After the comma that we have added at the end of the expression, begin to type in the word "Related."

32. Double-click the RELATED function within the dropdown list, as depicted.

Illustration 34: Select the RELATED Function…

NOTE: For a detailed overview of the DAX RELATED() function, see Stairway to PowerPivot and DAX - Level 1: Getting Started with PowerPivot and DAX.

33. Once the selector is dismissed, and =MAXX(FILTER(Product,RELATED( appears, begin to type in the words "Product Subcategory."
34. Scroll down and double-click / select the 'Product Subcategory'[EnglishProductSubcategoryName] column within the dropdown list, as shown.

Illustration 35: Select the 'Product Subcategory'[EnglishProductSubcategoryName] Column…

35. Once the selector is dismissed, and =MAXX(FILTER(Product, RELATED('Product Subcategory'[EnglishProductSubcategoryName] appears in the formula bar, type a right parenthesis ( " ) " ), and then an equals ( " = " ) sign, after 'Product Subcategory'[EnglishProductSubcategoryName]).

The syntax in the function bar, at this point, should be as shown here:

=MAXX(FILTER(Product,RELATED('Product Subcategory'[EnglishProductSubcategoryName])=

After the equals sign that we have added at the end of the expression, type in the following string:

"Tires and Tubes"),

The syntax in the function bar, at this point, should be as shown here:

=MAXX(FILTER(Product,RELATED('Product Subcategory'[EnglishProductSubcategoryName])="Tires and Tubes"),

36. After the comma at the end of the string we have added, begin to type in the word "Product," once again.
37. Scroll down and double-click / select the Product[StandardCost] column within the dropdown list, as shown:

Illustration 36: Select Product[StandardCost] Column…

38. Once the selector is dismissed, type a right parenthesis ( " ) " ) after Product[StandardCost].

The completed function should be as shown here:

=MAXX(FILTER(Product,RELATED('Product Subcategory'[EnglishProductSubcategoryName])="Tires and Tubes"), Product[StandardCost])

… and appears as presented in Illustration 37.

Illustration 37: The Completed MAXX() Function

In effect, we are submitting a filtered, related table as the first argument of the MAXX() function – a table filtered, via the DAX FILTER() function, to return, via the RELATED() function, only the rows in the Product table for which the related rows in the Product Subcategory table meet the condition Product Subcategory = "Tires and Tubes." (The FILTER() function is applied to the Product table using a value looked up in the Product Subcategory table.) The "maximum" is then performed on the Standard Cost associated with the rows in the resulting table.

39. Press the ENTER key to accept the syntax we have input, and to populate the new calculated column.

Let's name the new calculated column to distinguish it, as we have done with the calculated columns we have created before.

40. Right-click the new column header label (currently at its default of "CalculatedColumn1," once again), and select Rename Column from the cascading menu that appears, as we have done in earlier examples.
41. Name the newly added column "Std-Costed T & T MAX" to provide a unique column header name.

The newly-named column, with updated formula results, appears as partially shown.

Illustration 38: The Results Again Appear in Each Row of the New Column (Partial View)

Let's check our result with a simple test:

42. On the Product table, click the downward pointing selector button on the ProductSubcategoryKey label.
43. Within the Number Filters list of the selector that appears, clear the checkbox labelled (Select All), as depicted, to clear all checked boxes in the list.

Illustration 39: Clearing All Number Filters…

44. Scroll down in the Number Filters list and click the checkbox to the left of the number 37 (the ProductSubcategoryKey for "Tires and Tubes"), as shown.

Illustration 40: Setting the Filter at ProductSubcategoryKey 37…

45. Click OK to close and save the new filter setting.

The Product tab reappears, filtered for ProductSubcategoryKey 37. It contains eleven rows, as partially shown.

Illustration 41: Product Tab, Filtered for ProductSubcategoryKey 37 (Partial View)

46. Scroll to the right to the StandardCost column.

We can easily see, within the column containing the Standard Cost for each Product belonging to ProductSubcategoryKey 37, that the greatest StandardCost value is $13.09. This is reflected in the partial depiction of the filtered data set below.

Illustration 42: Easy Verification of the Maximum ProductSubcategoryKey

This provides easy visual verification that the value we obtained in our current exercise with the DAX MAXX() function is correct.

In the section that follows, we will access the PowerPivot calculated columns we have created from within a PivotTable. Moreover, we will get some hands-on exposure to putting the DAX MAX() and MAXX() function to work within a calculated measure we will create in the PivotTable.

PivotTable Measure

We will next work further with the DAX functions we have exposed in the PowerPivot calculated column section above – this time via calculated fields (called measures in Excel 2010 PivotTables using PowerPivot data). We do this keeping in mind the assertion we make throughout the Stairway to PowerPivot and DAX series that PowerPivot exists, in its fundamental role, to act as a data source for PivotTables. The difference, this time, will be that we will present values and calculations within a PivotTable.

As is known widely at this stage, in the evolution of PowerPivot in general, we can begin our work with a PivotTable from either the Excel workbook or from the PowerPivot window. From where we are in the PowerPivot window, we'll proceed by taking the following steps:

1. From the Product tab in the PowerPivot window, click the downward pointing arrow on the PivotTable button to cause the drop-down menu to appear.

As noted in other Levels of Stairway to PowerPivot and DAX, although we work with examples, from time to time, of some of the other options we see in the dropdown menu, we will work with a standalone PivotTable in this session. This will be the most efficient path to getting hands-on exposure to the underlying PowerPivot data and DAX, in general.

2. Click PivotTable in the dropdown menu we've exposed (the top selection), as shown.

Illustration 43: The PowerPivot PivotTable Drop-down Menu Appears…

We are returned to Excel, where we encounter the Create PivotTable dialog.

3. Ensure that the radio button labeled New Worksheet is selected, as depicted.

Illustration 44: Ensure the "New Worksheet" Option is Selected…

4. Click OK to accept the setting and dismiss the dialog.

The PivotTable placeholder / canvas, together with the PivotTable Fields list, appear on the new worksheet as presented:

Illustration 45: The PivotTable Canvas Appears on the New Worksheet (Compressed View)

We'll leave the placement of the new PivotTable at default.

NOTE: Because we cover the details for the first time in an earlier Level of the series, and particularly for those not already familiar with standard Excel PivotTables, Table 1 of Level 2: The DAX COUNTROWS() and FILTER() Functions and More About Calculated Columns and Measures gives a description for each of the areas / zones on the PivotTable Fields list. Other PivotTable basics are discussed there as well.

To gain some exposure to the use of the DAX MAX() and MAXX() functions within the PivotTable, we will replicate some of the steps we took with regard to the tabs upon which we operated in the PowerPivot window in the earlier part of this Level. In accomplishing this, we'll see how to approach what we did with the calculated column earlier, but this time we'll do so with a calculated measure in the PivotTable. Moreover, we will touch upon more PivotTable – specific considerations as we encounter them. But first, we will pull the calculated columns we created earlier into a new PivotTable we construct, to allow us to see the output of our calculation when we select it from the PivotTable Fields list.

5. Expand the Customer table within the PivotTable Fields list.
6. Scroll down the Customer table columns that appear, and right-click the LatestBirthDate column (the calculated column that we added in the earlier exercise).
7. Select Add to Values column from the context menu that appears above the PivotTables Field list, as shown.

Illustration 46: Adding the LatestBirthDate Calculated Column to the PivotTable Values Drop Zone

Because Excel recognizes the Date data type of the calculated column we have dropped into the Values zone, the new value field is assigned a Count summary, by default. This is obviously not what we currently seek, so we'll change the summarization next.

8. Within the Values drop zone of the PivotTable Fields portion of the PivotTable, click the downward pointing arrow on the newly appearing Count of LatestBirthDate.
9. Select Value Field Settings… from the cascading menu, as depicted.

Illustration 47: Modifying the Summarization Method for Count of LatestBirthDate

The Value Fields Settings dialog appears.

10. From the list labelled Summarize value field, select Max, as shown.

Illustration 48: Replacing the Default Count with MAX()

We immediately receive a message box, indicating that MAX() is not an option for selection with Date data types, as depicted.

Illustration 49: Error: "Cannot Summarize Date Types with Max"

So our dilemma is evident: We don't want to use the default COUNT() option, but the Value Field Settings won't let us choose MAX(). Thankfully we always have the option of applying the function via a new calculated field that we can easily create. This will provide us a means of observing how the LatestBirthDate calculated column performs within the PivotTable. Let's start by removing the Count of LatestBirthDate calculated column from the Values drop zone for the PivotTable.

11. Click OK to dismiss the message box.
12. Click Cancel on the Value Field Settings dialog to close it.
13. Click Count of LatestBirthDate in the Values drop zone, once again.
14. Click Remove Field from the cascading menu, as shown.

Illustration 50: Remove the Count of LatestBirthDate Field

The Values zone empties.

15. Click the PowerPivot tab, if necessary, atop the worksheet containing our current PivotTables.
16. Click the Calculated Fields button in the ribbon atop the tab (to the right of the PowerPivot Manage button, on the left side of the ribbon).
17. Select New Calculated Field… from the menu that appears, as presented here:

Illustration 51: Click New Calculated Field…

The Calculated Field dialog appears.

18. Ensure that Customer appears in the dropdown selector labeled Table name atop the dialog.
19. Type (or copy and paste) the following into the Calculated Field Name box of the dialog.

CF_LatestBirthDate

(We are simply naming the calculated field in a way to distinguish it from the entrained calculated column already in the PivotTable.)

20. Type (or copy and paste) the following syntax into the Formula input box of the dialog:

=MAX(Customer[LatestBirthDate])

21. Select Date in the Formatting Options section (the "Category" selections in bottom left corner) of the Calculated Field dialog.
22. Select the *3/14/2001 (also known as "Short Date") in the Format selector, as depicted.

Illustration 52: The Calculated Fields Dialog, with Our Input

23. Click OK to accept our input and dismiss the Calculated Field dialog.

The new calculated field, CF_LatestBirthDate appears at the bottom of the member listings of the Customer tree. The CF_LatestBirthDate field is also placed within the Values drop zone, which also triggers its appearance in the PivotTable to the left in the worksheet, as shown.

Illustration 53: The New Calculated Field Appears in Both Values Drop Zone and the PivotTable

We see that MAX() works differently in the Values drop zone, and, therefore, within the PivotTable itself, than aggregations we have performed with DAX functions within calculated fields in parallel articles of the series. Not only does the "Max of a Max" give us the correct answer, but we had to create a new calculation – and bypass the "built-in" function of at the level of editing the default (COUNT()) in the Value Field Settings of the Values drop zone.

Just to ensure we cover all the bases, let's create the same calculation "from scratch" for the PivotTable, using a calculated field, the MAX() function, and the base column within the PowerPivot window. But first, let's add some representative rows and columns to our PivotTable to enrich the overall display a bit.

We'll leave the new CF_LatestBirthDate in place, too, for comparison purposes as needed.

24. Click the checkbox to the immediate left of the LastName column, again within the expanded Customer table of the PivotTable Fields list.

This places the LastName field into the Rows drop zone, and its simultaneous appearance in the PivotTable to the left of CF_LatestBirthDate, as partially depicted.

Illustration 54: Adding LastName to the Rows Drop Zone

While the above presents exactly what we have asked for, listing the latest birth date alongside every customer is not a readily useful presentation – especially to an audience that might see it without understanding what we are attempting to do. Let's isolate the results of the new calculation to the individuals with whom the latest birth date corresponds.

25. Right-click BirthDate in the Customer table columns tree.
26. Select Add as Slicer from the context menu that appears, as shown.

Illustration 55: Making BirthDate a Slicer for the PivotTable

BirthDate appears as a floating slicer at some point near the PivotTable.

27. Scroll to the bottom of the slicer, and select the 12/26/1980 entry.

The rows in the PivotTable are now filtered to the two customers whose birth dates are the latest (maximum) date of 12/26/1980, as depicted.

Illustration 56: Filtering for the Most Recent Birth Date…

Next, we'll add the alternative calculated field, wherein we build the maximum birth date from scratch.

28. Click the Calculated Fields button in the ribbon atop the tab, once again.
29. Select New Calculated Field… from the menu that appears, as we did earlier.

The Calculated Field dialog appears, as before.

30. Ensure that Customer is selected in the dropdown selector labeled Table name atop the dialog.
31. Type (or copy and paste) the following into the Calculated Field Name box of the dialog.
32. CF2_LatestBirthDate

Select the function button ("fx") appearing to the left above the Function input box.

Illustration 57: Select the Function (fx) Button…

The Insert Function dialog appears.

33. Click the downward-pointing arrow on the selector appearing atop the dialog under the Select a category label.

The DAX function categories that appear mirror those in the Insert Function dialog we accessed in creating a calculated column in the PowerPivot window earlier.

34. Select the Statistical category, on the Select a category dropdown, as we did before.
35. In the Select a function list that populates for the Statistical category, select MAX().
36. Click OK to confirm the selection and dismiss the Insert Function dialog.

The MAX keyword, proceeded by the "=" operator and followed by a left parenthesis ( "(" ), appears in the formula bar, with a function usage tooltip appearing underneath the function.

37. Following the left parenthesis inserted by our selection of MAX, begin to type in the word "Customer."
38. Scroll down and double-click / select the Customer[BirthDate] column within the dropdown list.

Illustration 58: Select the Customer[BirthDate] Column

39. Once the selector is dismissed, and =MAX(Customer[BirthDate] appears in the formula bar, type a right parenthesis ( ")" ) at the current end of the syntax, to close the MAX() function.

The syntax, at this point, should be as shown here:

=MAX(Customer[BirthDate])

40. In the Formatting section in the lower left corner of the dialog, select Date in the Category selector.
41. In the newly-enabled Format selector to the right, select the Short Date format string, *3/14/2001.

The completed Calculated Field dialog appears as shown.

Illustration 59: The Calculated Field with Our Input

42. Click OK to save settings and dismiss the Calculated Field dialog.

We now have only to add the new calculated field to the Values drop zone to incorporate it into our PivotTable.

43. Click the checkbox to the immediate left of the CF2_LatestBirthDate entry.

This places the CF2_LatestBirthDate field into the Values drop zone, which also triggers its appearance in the PivotTable to the left in the worksheet, as depicted.

Illustration 60: The "From Scratch" Calculation is Added to the PivotTable

We can see, unsurprisingly, that same result is generated with both calculated fields.

Next, let's look at MAXX() from the perspective of the PowerPivot environment. First, we'll bring the calculated column, which we named Std-Costed T & T MAX, into a new PivotTable.

44. Go to a blank worksheet (I will access Sheet2 in the workbook pictured above), to start in a fresh environment.
45. Place the cursor in cell A1.
46. Click the Insert tab above the ribbon atop the worksheet
47. Click the PivotTable button (to the far left of the ribbon, by default), as shown.

Illustration 61: Creating a New PivotTable on a Blank Worksheet

The Create PivotTable dialog appears.

48. Click / select the radio button labelled Use an external data source in the section atop the dialog, labelled Choose the data that you want to analyze.
49. Click the Choose Connection button directly underneath the Use an external data source selection.
50. Click the Tables tab atop the Existing Connections dialog, which appears next.
51. On the Tables tab, double-click the Tables in Workbook Data Model selection within the This Workbook Data Model list.

Illustration 62: Sourcing Our PivotTable with the Existing Workbook Data Model

The Connection dialog is dismissed and we return to the Create PivotTable dialog, which appears, with our input at this point, as depicted.

Illustration 63: The Create PivotTable Dialog with Our Input

52. Leaving the remaining settings of the Create PivotTable dialog at default, click OK to save settings and dismiss the dialog.

We are returned to the worksheet, where the new PivotTable template and PivotTable Field List appear, as shown.

Illustration 64: The New PivotTable Stands Ready…

53. Expand the Product table within the PivotTable Fields list.
54. Scroll down the Product table columns that appear, and click the checkbox to the immediate left of the Std-Costed T & T MAX column at bottom of the list (the second calculated column that we added in the earlier exercise).

Std-Costed T & T MAX is added to the Values drop zone as a Sum, by default. While we have come to expect this as the default behavior, the value that appears in the PivotTable as a result is meaningless.

Illustration 65: Adding the Std-Costed T & T MAX Calculated Column to the PivotTable Values Drop Zone

Because summing is not the aggregation that we currently seek, we'll change the summarization next to return the maximum value intended when we created the calculated column (using the MAXX() function).

55. Within the Values drop zone of the PivotTable Fields portion of the PivotTable, click the downward pointing arrow on the newly appearing Sum of Std-Costed T & T MAX.
56. Select Value Field Settings… from the cascading menu, as we did earlier.

The Value Fields Settings dialog appears.

57. From the list labelled Summarize value field, select Max.
58. Click OK to save the modifications, and close the Value Field Settings dialog.

Upon replacing the default summing mode with Max, we see that the value in the PivotTable changes to the expected calculated column value of $13.09.

Illustration 66: Replacing the Default Sum with Max

So, in this case, we see that we can obtain the desired summarization with Max of the calculated column within the Values drop zone.

We'll conclude our exploration in this Level with a PivotTable-based recalculation of the Std-Costed T & T MAX calculated column, which we constructed within the Products table using the following syntax:

=MAXX(FILTER(Product,RELATED('Product Subcategory'[EnglishProductSubcategoryName])="Tires and Tubes"),Product[StandardCost])

Let's add a calculated field in the PivotTable to give us an alternative, direct way to achieve the same end as Std-Costed T & T MAX. We'll leave the new CF_Std-Costed T & T MAX in place, for comparison purposes as needed, as we have done in earlier exercises.

59. Click the PowerPivot tab once again, atop the worksheet containing our current PivotTable.
60. Click the Calculated Fields button in the ribbon atop the tab (to the right of the PowerPivot Manage button in the upper left of the ribbon, by default).
61. Select New Calculated Field… from the menu that appears, as we did earlier.

The Calculated Field dialog appears.

62. Select Product in the dropdown selector labeled Table name atop the dialog.
63. Type (or copy and paste) the following into the Calculated Field Name box of the dialog.

CF_Std-Costed T & T MAX

(We are simply naming the calculated field in a way to distinguish it from the calculated column we've already entrained into the PivotTable.)

64. Type (or copy and paste) the following syntax into the Formula input box of the dialog:

=MAXX(FILTER(Product,RELATED('Product Subcategory'[EnglishProductSubcategoryName])="Tires and Tubes"),Product[StandardCost])

This is the syntax we used in creating the Std-Costed T & T MAX calculated column in the Product tab of the PowerPivot window.

65. Select Currency in the Formatting Options section (the "Category" selections in bottom left corner) of the Calculated Field dialog.
66. Leave other settings at default.

The Calculated Field dialog appears, with our input, as depicted.

Illustration 67: The Calculated Fields Dialog, with Our Input

67. Click OK to accept our input and dismiss the Calculated Field dialog.

The new calculated field, CF_Std-Costed T & T MAX appears at the bottom of the member listings of the Product tree), and also appears within the Values drop zone, as well as the new PivotTable, alongside the calculated column that we added earlier, as shown.

Illustration 68: The New Calculated Field Appears…

We can see, too, that the new No. Std-Costed Selections calculation appears to work as expected.

The examples we have considered with MAX()and MAXX() in this Level of the series are only relatively simple instances of what we can accomplish with their use. We would, of course, likely need to make changes to render varying contexts (such as might be needed for various hierarchical presentations, etc.), but, within the scope of this article, we can see how to compose the calculation both in PowerPivot and in the PivotTable. We will revisit many of the scope complications we can expect to encounter, and the means we have of resolving those challenges, in other articles of the Stairway to PowerPivot and DAX.

As we consistently note in other Levels, particularly where "X-" iterator DAX functions are involved, we can analyze and report upon data based upon date ranges, among other criteria, and create conditions (from simple to complex) that further refine the data, and / or include data taken from multiple tables. Once again, it is easy to see how DAX empowers us to generate and support comprehensive BI within our Excel environments, transforming the relatively limited options we once had with simple spreadsheets into sophisticated and robust reporting and analysis solutions.

Summary

In this Level of the Stairway to PowerPivot and DAX series, we exposed the DAX MAX() and MAXX() functions. We discussed the general purposes and operation of each of these two DAX functions, and then focused upon using each in general, as well as comparing and contrasting ways that each can be employed, particularly in situations where we can combine them with other functions to achieve results like those we might need to generate in client or employer environments.

In like manner to our standard approach to every DAX function we explore within the Stairway to PowerPivot and DAX series, we examined the syntax surrounding each of the MAX() and MAXX() functions, undertaking illustrative examples of the uses of each in practice exercises, and then exploring the results datasets we obtained in the practice examples. We combined each of MAX() and MAXX() with other functions to deliver illustrative answers to business questions, within both PowerPivot calculated columns and PivotTable calculated fields we built in the section that followed. As we put calculations to use within the PivotTable, we compared and contrasted the values returned, explaining the reasons behind differences, and discussing considerations in choosing which calculation approaches to take in meeting business requirements in the business world.