Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stairway to PowerPivot and DAX - Level 4: The DAX BLANK() Function

By Bill Pearson, (first published: 2013/03/18)

The Series

This article is part of the Stairway Series: Stairway to PowerPivot and DAX

Data Analysis Expressions (DAX) is the standard PowerPivot formula language that supports custom calculations in PowerPivot tables and Excel PivotTables. While many of the functions used in Excel are included, DAX also offers additional functions for carrying out dynamic aggregation and other operations with your data. Bill's new series serves as a progressive introduction to PowerPivot and DAX formulas, examining the functions, operators and values involved, and offering a wealth of practical examples.

In this Level, we examine the DAX BLANK() function, which returns a blank (or an empty value). As we shall see in the sections that follow, it is important to understand the nature and behavior of missing or empty values in a DAX expression before we can fully control its output. The BLANK() and ISBLANK() (treated in the next level of this Stairway) functions are two important means of enforcing such control.

As we do customarily in the Levels of this series, we'll discuss ways in which BLANK() can be employed, first, in this Level, in "standalone" examples for the function, and then, in Level 5, in situations where we combine it with other functions (specifically the ISBLANK() function), to achieve a result similar to something we might need to generate for clients or employers in our own environments. As part of our exploration of the BLANK() function, we will:

  • Discuss the nature of missing or empty values in a DAX expression;
  • Examine the syntax involved in using the function;
  • Undertake illustrative examples of the uses of the function in practice exercises;
  • Briefly discuss the results datasets we obtain in the practice examples.

In addition to introducing the BLANK() function, we will examine the output we obtain via a calculated column we construct within the PowerPivot window in the practice session that follows. We will further create a measure, within a new PivotTable, that puts the BLANK() function to work, to replicate the behavior that we have examined in the calculated column created in the PowerPivot window. As is typically true within the Levels of this series, we will compare and contrast differences in use and behavior of the DAX function under immediate consideration in calculated columns and calculated members in general, and discuss criteria to consider in choosing which of these calculation types to exploit to meet requirements we encounter in supporting employers and clients.

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

As we'll note throughout, to obtain the most benefit from the Levels of the Stairway to PowerPivot and DAX series, you will need to have installed the PowerPivot for Excel 2010 add-on on your machine, or to have installed Excel 2013 and enabled it to work with PowerPivot. You will also need to have installed SQL Server 2008 or above (including Analysis Services) 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 Levels of preparation, see the installation section of the charter Level of this series, Level 1: Getting Started with PowerPivot and DAX.

Preparation for the Practice Exercises in this and the Next Levels

Once we';ve got the PowerPivot for Excel 2010 add-in (or Excel 2013), SQL Server 2008 or above, 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 various steps. The objective is 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. 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 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 2010 (or above).

2. Within a new, blank workbook, above the Excel ribbon, click the PowerPivot tab.

Click the PowerPivot Tab

Illustration 1: Click the PowerPivot Tab

The PowerPivot ribbon appears.

3. Click the PowerPivot Window Launch button, appearing at the left of the newly appearing PowerPivot toolbar.

The PowerPivot Window Launch Button on the PowerPivot Ribbon

Illustration 2: The PowerPivot Window 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, and defaulting to the PowerPivot Home tab.

The PowerPivot Window, Associated with the Workbook, Appears

Illustration 3: The PowerPivot Window Associated with the Workbook Appears

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

4. Click the From Database button on the Home tab of the PowerPivot window.

5. Select From SQL Server on the drop-down menu that appears next.

Select From SQL Server in the From Database Dropdown

Illustration 4: Select "From SQL Server" in the From Database Dropdown

The Table Import Wizard dialog opens next.

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

AdventureWorksDW2008R2

7. Select a server, by either clicking the selector (downward pointing arrow) on the right side of the box titled Server name, or by typing the appropriate server name directly (this will, of course, save the time that the PC takes to browse for server names).

8. Enter the authentication as required for the local environment (ideally selecting Use Windows Authentication).

9. Select AdventureWorksDW2008R2 (or the equivalent in the respective Analysis Services 2008 through 2012 environment in which you are working), 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.

The Table Import Wizard Dialog

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

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

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

Test Connection Succeeded

Illustration 6: "Test Connection Succeeded"

11. Click OK to dismiss the message box.

12. Click the Next button at the bottom of the Table Import Wizard dialog.

13. 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.

Select from a list of tables

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

14. Click the Next button.

PowerPivot loads the tables and views from the selected database into the Select Tables and Views page of the Table Import Wizard that appears next.

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

Table to Select (Source Table)

Change Name to (Friendly Name):

DimCustomer

Customer

DimDate

Date

DimProduct

Product

DimProductCategory

Product Category

DimProductSubcategory

Product Subcategory

FactInternetSales

Internet Sales

FactResellerSales

Reseller Sales

Table 1: "Select Table and Views" Dialog Input

The Select Tables and Views dialog appears, as partially shown, with our selection and related tables checked.

Selecting Tables (Partial View)

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 organization.

16. 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.

Success is Indicated, Along with a List of Tables Imported

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

17. 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.

Imported Data, with a Tab for Each Table

Illustration 10: Imported Data, with a Tab for Each Table

NOTE: For more detailed information about the PowerPivot window itself, please see Level 1: Getting Started with PowerPivot and DAX.

18.Click the PivotTable button atop the current PowerPivot window, as shown.

Click the PivotTable Button atop the PowerPivot Window

Illustration 11: Click the PivotTable Button atop the PowerPivot Window

We are returned to the Excel worksheet, where the Create PivotTable dialog appears as shown.

Illustration 12: The Create PivotTable Dialog Appears

19. Leaving the radio button in the default position of New Worksheet, click OK.

20. Click File → Save As from the main menu atop the worksheet, and save the file to a convenient position as follows:

21.Select Save As from the dropdown menu that appears, as shown in Illustration 11.

Select Save As from the Selector within the PowerPivot Window

Illustration 13: Select Save As from the Selector within the PowerPivot Window

22.Save the worksheet as ST_DAX04-1.xlsx, placing it in a convenient location.

23.Leave the PowerPivot Window open for the practice session below.

We are ready, at this stage, to get some hands-on exposure to two new DAX functions, BLANK() – in this level - and ISBLANK() – in the next level of our series. Along the way, we will continue to build experience in the general use of PowerPivot to meet business requirements.

Before we dive into these new functions, though, let's take a look at how PowerPivot and Excel handle empty or missing values in general.

Empty Values

As a prelude to our examination of the nature and operation of the DAX BLANK() and ISBLANK() functions, we should gain an understanding of how empty values are treated in

PowerPivot and Excel. The primary reason this becomes important is that empty values often result in calculation errors and / or unexpected results. We must understand how empty or missing values behave in a DAX expression, as well as how we can use the BLANK() function to return an empty cell in a calculated column or in a measure, to effectively control the results of a DAX expression under these conditions.

In SQL Server, as well as within most relational databases, a missing value is treated as "Null." It is typical in relational environments for calculations involving a Null to return a Null by default, although SQL Server and other environments allow for modification of this behavior. In Analysis Services, as well as in other multidimensional databases, missing values are typically treated, depending upon the source data type, as empty strings or zeros.

PowerPivot designates a missing value as a "Blank." When we add a number to a blank value, the number itself is returned, instead of a Null or another blank. In handling missing values, blank values, or even empty cells, DAX uses a value of BLANK. BLANK comprises a way to identify these conditions, and is not a real value itself. To obtain a value of BLANK, which is different from an empty string, we employ the BLANK() function, as we'll describe below.

Excel differs from PowerPivot in its treatment of empty values. Excel interprets an empty value as a zero ("0") when that value is used within a summing or multiplication operation. Moreover, Excel returns an error if empty values are subjected to division, or used within a logical expression.

The BLANK() Function

Introduction

According to the TechNet Library, the DAX BLANK() function "returns a blank." BLANK(), a fundamental and important function, is a member of the Excel Text Functions group.

BLANK() is useful in numerous contexts, and is typically used in conjunction with other functions. A common example of its use, which we'll see in an example later, is a situation where it controls the output of a ratio where, say, the denominator may be empty or a zero. In such a scenario, we might use BLANK() to return a blank value, or empty cell in the PowerPivot window, which it would always do in this expression:

= BLANK()

BLANK(), therefore, offers utility whenever we need to return a blank value, even though the expression above serves no useful end on its own.

We will explore the syntax for the BLANK() function after a brief discussion in the next section. We will then gain some hands-on exposure to its use, within practice examples constructed to support hypothetical business needs. 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 BLANK() function returns a blank, plain and simple. To understand how the function can be most useful to us in given business situations, we need to understand how the "empty value" that BLANK() returns can, depending upon the calculation into which it is inserted, return a blank, which, as we'll see, is often the desired outcome.

In DAX, an expression returns a blank in many cases when it contains any "blank" that is operated upon via the expression. This would be true in an instance, for example, where we simply multiplied a constant against a value that was empty, like this:

= FactInternetSales[SalesAmount]*.06

where we are computing a sales tax amount upon an empty or null SalesAmount value in the underlying data. Many (but not all) arithmetic and logical operations generate a blank, in cases where one or both of the "participants" in the operation are blank, it simply depends upon the operation.

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

Syntax

To return a blank using BLANK(), we simply supply the function, with nothing in the parentheses to the right of the BLANK keyword, like this:

BLANK()

For example, let's get a feel for the general use of BLANK() within the InternetSales tab of the PowerPivot window, where we have performed the imports described in the Preparation section above. Let's say we want to put BLANK() to use in a scenario where we wish, within a calculated column, to return a blank in any row where we have a Sales Amount of less than $ 500.00, but to return the actual Sales Amount value in any case where the value is equal to or greater than $500.00. A straightforward way to accomplish this would lie within the following formula:

=IF(InternetSales[SalesAmount]< 500, BLANK() , "> $ 500.00" )

If we looked at the new calculated column on the tab, at a point where we have both possible conditions in the SalesAmount column, we would see something like this:

Calculated Column Using BLANK() on the InternetSales Tab (Partial View)

Illustration 14: Calculated Column Using BLANK() on the InternetSales Tab (Partial View)

As is obvious, within the small view we see above, the calculation delivers the required "bucketing:" in rows where Internet Sales are less than $ 500, a blank, or empty, is returned, whereas in those rows where we have Internet Sales greater than or equal to the same value, our string of ">$ 500.00" appears. In this simple example, we witness the action of the BLANK() function: to literally return a blank under desired conditions.

Let's get some hands-on practice with BLANK() in the section that follows:

Practice

To reinforce our understanding of the basics we've covered so far, we will follow our typical pattern of employing the function under examination in this Level within the definition of a couple of calculations: First, we'll work with BLANK() via a calculated column within a tab of the PowerPivot window. Next, we'll put the function to use via a calculated measure within a PivotTable of a worksheet within the associated Excel workbook. We will examine the use of BLANK() in combination with other functions with which we work in other Levels. The intent, as in all the practice sessions of the Stairway to PowerPivot and DAX series, is to demonstrate the operation of each of the functions we examine in a straightforward, memorable manner.

Let's move 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 Column

Let's take a relatively understandable business need: We wish to determine the percent discount (when a discount is taken) in each Reseller Sales transaction experienced in the Adventure Works sample. The math is simple: divide the Discount Amount for each row (transaction) in the Reseller Sales table by the respective Reseller Sales Amount. But, because discounts in the table are sparse, we wish to build further logic into the calculation to handle "zero" Discount Amounts within our percentage calculations in such a manner that a blank – not a zero – is returned. (The "blank" presentation is driven by a desire to distinguish "zero percent" from "no discount taken – a minor, but not uncommonly chosen, visual effect.)

We will deliver the desired result with a calculation whose components include the DAX BLANK() function.

1. Return to the worksheet we created in the Preparation for the Practice Exercises in this Levelsection above, ensuring that the PowerPivot Window is open, where we left it earlier.

2. Return to the PowerPivot Window.

3. Click the Reseller Sales tab.

Click the Reseller Sales Tab

Illustration 15: Click the Reseller Sales Tab

4. Scroll to the right, and then click the top row in the column labeled "Add Column," which appears on the far right side of the tab, as shown.

Click the Top Row in the Column Labeled Add Column

Illustration 16: Click the Top Row in the Column Labeled "Add Column"

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

Select the Function (fx) Button

Illustration 17: Select the Function (fx) Button

The Insert Function dialog appears.

6. 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

Insert Function Dialog with Expanded Category Selector

Illustration 18: Insert Function Dialog with Expanded Category Selector

7. Select the Logical category.

8. In the Select a function list that populates for the Logical category, select the IF() function, as shown.

Select the IF() Function

Illustration 19: Select the IF() Function

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

The IF 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.

The Beginning of the IF() Function, along with Tooltip, Appears

Illustration 20: The Beginning of the IF() Function, along with Tooltip, Appears

10. Because we know that we need now to add the appropriate table name / expression, begin to type in "Reseller Sales."

As we begin to type the table name, AutoComplete immediately presents us with a list of functions and tables from which to select, as shown.

Illustration 21: Table as Suggested by AutoComplete

11. Double-click 'Reseller Sales'[DiscountAmount] within the dropdown list to select the column, as depicted in Illustration 20.

Select the 'Reseller Sales'[DiscountAmount] Column

Illustration 22: Select the 'Reseller Sales'[DiscountAmount] Column

12. Once the selector is dismissed, and =IF('Reseller Sales'[DiscountAmount] appears in the formula bar, type in the following syntax to the right:

=0, BLANK(),

The syntax to this point should be as shown here:

=IF( 'Reseller Sales'[DiscountAmount]= 0 , BLANK(),

At this stage, the calculation is saying "if the Discount Amount is zero (the logical test), return a blank (the ‘value if true')." We now need to provide the corresponding ‘value if false.'

13. Type the following syntax to the right of the existing expression:

'Reseller Sales'[DiscountAmount]/'Reseller Sales'[SalesAmount])

A completed formula appears within the formula bar.

The Completed Formula Appears

Illustration 23: The Completed Formula Appears

14. Press the Enter key.

The column updates and the formula results appear in each row of the new calculated column, as partially shown below. (Because discounts are sparse in the Reseller Sales table – and zeros exist in most of the Discount Amount rows – the below view is scrolled to capture some sample populated fields in the calculated column.)

The Results Appear in Each Row of the New Column (Partial, Compressed View)

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

We note that the value shown in the populated rows appears to be correct. (We would likely format the value, to indicate its "percentage" nature, at the PivotTable report level, as various reporting / analysis needs with regard to decimalization, etc., might differ among presentations.) Let's name the column in accordance with its ultimate intended purpose, at this juncture.

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

Renaming the Column to its Ultimate Name

Illustration 25: Renaming the Column to its Ultimate Name

16. Name the newly added column "Disc Perc of Sales" for Discount Percent of Sales.

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

PivotTable Measure

In its most fundamental and common use, as we have noted throughout the Stairway to PowerPivot and DAX series, PowerPivot exists to act as a data source for PivotTables. In this section, we will work further with the DAX function we have exposed in the PowerPivot calculated column section above, except this time, we will present calculations and values within a PivotTable.

We can begin our work with a PivotTable from either the Excel workbook or from the PowerPivot window. Because we're already in the latter, we will proceed from there by taking the following steps:

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

The PowerPivot Drop-down Menu Appears

Illustration 26: The PowerPivot Drop-down Menu Appears

While we will work with examples, at various times throughout our series, of some of the other options we see in the dropdown menu (single PivotChart, Chart & Table, Two Charts, Four Charts, and Flattened PivotTable), we will build a single PivotTable in this session, and throughout most of the practice sessions, of our series. This will be the most straightforward (and rapid) avenue to getting hands-on exposure to the underlying PowerPivot data and DAX.

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

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

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

Ensure the New Worksheet Option is Selected

Illustration 27: 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 PowerPivot Field List, appear on the new worksheet as depicted (compressed view).

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

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

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 PowerPivot Field List. Other PivotTable basics are discussed there as well.

To gain some exposure to the use of the DAX BLANK() function within the PivotTable, we will replicate some of the steps we took with regard to the Reseller Sales table, 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 calculation we created earlier into a new PivotTable we construct, to allow us to compare the results of the calculated column to the calculated measure we create here.

5. Expand the Reseller Sales table in the PowerPivot Field List by clicking the "+" sign to its immediate left.

The columns of the table, with which we worked in the PowerPivot window in the section above, appear.

Reseller Sales Table in the PowerPivot Window, Expanded to Show Fields

Illustration 29: Reseller Sales Table in the PowerPivot Window, Expanded to Show Fields

The last field in the expanded list, of course, represents the calculated column that we created, and within which we employed the DAX BLANK() function, in the PowerPivot window earlier. We will pull this calculation into our new PivotTable like any other field at this point.

6. Within the PivotTable Field List, inside the expanded Reseller Sales table, scroll to the SalesOrderNumber and SalesOrderLineNumber columns.

7. Drag SalesOrderNumber to the Row Labels drop zone.

8. Drag SalesOrderLineNumber to the Row Labels drop zone, placing it underneath SalesOrderNumber.

The Row Labels drop zone appears, with our two additions, as shown.

Creating the Rows Axis of a New PivotTable

Illustration 30: Creating the Rows Axis of a New PivotTable

9. Again within the Reseller Sales table, click the checkbox to the immediate left of the following columns:

DiscountAmount

SalesAmount

Disc Perc of Sales

The Disc Perc of Sales calculation appears at the bottom of the column listings for the table, and, of course, represents the calculated column we added in the PowerPivot Window earlier. Checking the columns above places each in the Values drop zone (in the order in which we check them) of the PivotTable Field List, where it appears with "Sum of" preceding its name, as shown.

Adding the Calculated Column to the PivotTable

Illustration 31: Adding the Calculated Column to the PivotTable

Because the new PivotTable contains many rows, we will "slim it down" a little for easy review. To do so, we will select just a few Sales Orders to include in the PivotTable.

10. In the upper left corner of the PivotTable, click the cell just underneath the Row Labels selector (the cell containing Sales Order SO43659) to set the context of the dropdown selector, as depicted.

Select the Top Left Work Order Cell

Illustration 32: Select the Top Left Work Order Cell

11. Click the downward pointing arrow of the Row Labels selector, just above the selected cell.

12. Deselect the (Select All) box by clicking the checkmark currently in place, as shown.

Deselecting by Removing the Check in the (Select All) Box

Illustration 33: Deselecting by Removing the Check in the (Select All) Box

Next, let's select a small, contiguous set of Sales Orders to limit the size of the PivotTable with which we will be working.

13. Within the selector, scroll to, and click the check boxes to the immediate left of, each of Sales Orders number SO69401, SO69402, SO69403, and SO69404.

Select Sales Orders

Illustration 34: Select Sales Orders SO69401, SO69402, SO69403, and SO69404

14. Click OK to apply the filter and to dismiss the selector.

Let's focus our attention on the calculated column from the PowerPivot Window that we have added to the (dramatically smaller) PivotTable that now appears. Beginning with the top Sales Order, SO69401, we see that, on the individual lines of the Sales Order, the math appears to work correctly, as well as the logic we';ve enforced to return blank values in cases where the DiscountAmount is zero. For example, on Sales Order Line Number 10, we see that the Disc Perc of Sales calculation delivers 0.052631579, which agrees with the calculation of DiscountAmount / SalesAmount, or 3.9556 / 75.1564.

An issue arises when we test the calculation at the rolled up Sales Order level. In this case, on the total line for Sales Order SO69401, the Disc Perc of Sales calculation delivers 0.251342626, which is incorrect: the Discount Percent of Sales should actually approximate 0.004965131, which differs from what we see in the total line for the Disc Perc of Sales (.251342626).

As we saw in working with the same calculation within the PowerPivot Window in the earlier section, the correct value was being computed at the row level (see Illustrations 23 and 24). It is important to note, as we have pointed out in other Levels of this series, that, when the value is aggregated in the PivotTable, the result is not correct in many cases due to the operation of the SUM() function, which is employed by default when we place a numeric column into the Value drop zone of a PivotTable, among other context-related considerations.

To illustrate further this concept, let's set out to create a calculated measure in the PivotTable that correctly calculates the Disc Perc of Sales value at Sales Order Line and Sales Order levels.

15. Within the PowerPivot Field List, once again, right-click the Reseller Sales table label.

16. Select Add New Measure… from the context menu that appears, as shown.

Select Add New Measure

Illustration 35: Select "Add New Measure …"

The Measure Settings dialog appears.

17. Type (or copy and paste) the following into the Measure Name (while leaving the default copied label in place within the Custom Name) box of the dialog.

Disc Perc Total Sales

NOTE: Here we are giving a name that is different from the name we gave the corresponding calculated column in the PowerPivot Window in the section above, simply to make it easy to distinguish the two calculations when they appear within the same PivotTable.

18. Click the function button ("fx") to the right of the Formula label, above the Formula input box of the dialog.

The Insert Function dialog appears.

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

20. Select the Logical category within the expanded category selector atop the Insert Function dialog, just as we did in the earlier exercise.

21. Select the IF() function, once again, as depicted.

Select the IF() Function

Illustration 36: Select the IF() Function

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

The IF keyword, proceeded by the "=" operator and followed by a left parenthesis, once again, appears in the Formula input box of the Measure Settings dialog.

23. To the immediate right of the left parenthesis (that appears to the right of the IF keyword), begin to type in Reseller Sales.

As we begin to type the table name, AutoComplete again presents us with a list of functions and tables from which to select:

PowerPivot Functions / Tables

Illustration 37: Functions / Tables as Suggested by AutoComplete

24. Double-click ‘Reseller Sales'[DiscountAmount], as we similarly did at the PowerPivot level earlier, to ensure its selection.

25. Once the selector is dismissed, and =IF('Reseller Sales'[DiscountAmount] type the following syntax to the right, as we did for the similar calculated column in the PowerPivot window in the earlier section of this Level:

=0, BLANK(),

The formula string appears in the formula input box, at this stage, as shown.

PowerPivot Formula Input Box

Illustration 38: The Formula Input Box with Formula at this Stage<

As we noted, at this point in creating the corresponding calculated column in the earlier section, our calculation is stating that, if the logical test ("Discount Amount is zero") is true, return a blank. Next, we need to add the value to return if the logical test is false.

26. Type the following syntax to the right of the existing expression within the formula input box of the Measure Settings dialog:

'Reseller Sales'[DiscountAmount]/'Reseller Sales'[SalesAmount])

The completed formula string appears, within the formula input box, as depicted.

The Complete Text in the Formula Input Box

Illustration 39: The Complete Text in the Formula Input Box

The formula string replicates the string we used in creating the Disc Perc of Sales (for Discount Percent of Sales) calculated column in the PowerPivot window earlier.

27. Click the Check formula button.

An error message is returned.

A PowerPivot Error

Illustration 40: An Error Regarding Context is Returned

The message indicates that "the value for column 'DiscountAmount' in table 'Reseller Sales' cannot be determined in the current context." It becomes apparent, then, that a calculation that works at the row level, as we saw in our earlier example within the calculated column of the PowerPivot window, fails to generate the correct values in the PivotTable.

What we need, in fact, to do is to pay attention to context, a concept I discuss throughout the Levels of the Stairway to PowerPivot and DAX series. The idea, of course, is to perform the calculation, via our new measure, at the cell level of the new PivotTable and not at the row level of the PowerPivot window. In this case, we must generate the desired value by calculating a ratio that divides the total of 'Reseller Sales'[DiscountAmount] by the total of 'Reseller Sales'[SalesAmount].

Having attempted to use the same DAX expression we employed earlier, only to have received the context error as noted, let's go back and make a couple of minor modifications to our new measure.

28. Modify the syntax currently within the formula input box of the Measure Settings dialog, to place each occurrence of 'Reseller Sales'[DiscountAmount] and 'Reseller Sales'[SalesAmount] within the SUM() function. In other words, modify the existing syntax within the input box from this:

=IF('Reseller Sales'[DiscountAmount] =0, BLANK(), 'Reseller Sales'[DiscountAmount] / 'Reseller Sales'[SalesAmount])

… to this:

=IF(SUM('Reseller Sales'[DiscountAmount]) =0, BLANK(), SUM('Reseller Sales'[DiscountAmount])/SUM('Reseller Sales'[SalesAmount]))

The completed formula string appears, within the formula input box, as depicted.

The Complete, Modified Text in the Formula Input Box

Illustration 41: The Complete, Modified Text in the Formula Input Box

29. Click the Check formula button, once again.

The message returned this time indicates "no errors in formula," as shown.

No Errors in the PowerPivot Formula

Illustration 42: "No Errors in Formula" is Indicated

30. Click the OK button to accept our changes, and to dismiss the Measure Settings dialog.

The dialog box disappears, and the newly calculated measure appears in a column on the right side of the PivotTable. Let's take a look at the data to ascertain that the values generated by the new measure are correct.

The New Measure Proves to Be Correct

Illustration 43: The New Measure Proves to Be Correct

It's easy to see that, while both calculations appear to return the correct percentage values row-by-row in the individual lines of the sales order selected above, the first calculation (generated when we pulled it into the corresponding calculated column from the PowerPivot window into the PivotTable, by dropping the calculated column from the PivotTable field list into the Value drop zone) simply sums the line item calculations and generates a percentage at the sales order level that is incorrect.

In summary, then, it becomes evident that the value of a calculated column, which is evaluated during data refresh, uses the current row as a context. A PivotTable measure, by contrast, operates upon aggregations of data as defined by the context of the current cell. Source tables are filtered based upon the cell's coordinates, and data is aggregated and calculated via this filter. Put another way, there is no way to reference a single row in a given measure's DAX expression because a measure always operates upon aggregations of data subject to the evaluation (or "filter") context.

We will gain further exposure to the BLANK() function when we use it in tandem with the ISBLANK() function in the level that follows next in our series.

1. From the main menu of the workbook, select File -- Save As

2. Name the file ST_DAX04-1.xlsx, and save it in a meaningful location.

3. Exit Excel 2010 as desired.

As I emphasize in other Levels, understanding the nature and operation of, the differences between, and the optimal uses for, calculated columns and calculated measures is critical to sound and efficient reporting and analysis when working with PowerPivot. We work with these concepts throughout the Levels of Stairway to PowerPivot and DAX, embellishing and enhancing our technique as the series evolves. Practice and further experimentation with these and other components will ensure that we become fluent in the many options and opportunities that PowerPivot offers the knowledgeable user.

Summary

In this Level of the Stairway to PowerPivot and DAX series, we exposed the DAX BLANK() function. We discussed the general purposes and operation of BLANK(), and then focused upon using BLANK() in general, as well as illustrating ways to leverage the combination of BLANK() and other functions to deliver approaches to meeting business needs. As part of our discussion, we examined the syntax surrounding BLANK(), undertaking illustrative examples of the uses of the function in practice exercises, and then exploring the results datasets we obtained in the practice examples.

We worked with the combination of BLANK() and other functions to deliver illustrative answers to business questions, within both a PowerPivot calculated column and a PivotTable calculated measure we built in the section that followed. Once we had replicated, in the PivotTable, the calculated column we had created earlier in the PowerPivot window, we compared and contrasted the values returned by each, explaining the reasons behind their differences, and discussing considerations in choosing which of these two calculation approaches to take in meeting business requirements in our own environments.

This article is part of the Stairway to PowerPivot and DAX Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 6398 | Views in the last 30 days: 106
 
Related Articles
ARTICLE

Stairway to PowerPivot and DAX - Level 2: The DAX COUNTROWS() and FILTER() Functions

Bill Pearson, business intelligence architect and author, exposes the DAX COUNTROWS() and FILTER() f...

ARTICLE

Stairway to PowerPivot and DAX - Level 5: The DAX ISBLANK() Function

Business Intelligence architect, Analysis Services Maestro, and author Bill Pearson exposes the DAX ...

ARTICLE

Stairway to PowerPivot and DAX - Level 6: The DAX SUM() and SUMX() Functions

Business Intelligence architect, Analysis Services Maestro, and author Bill Pearson exposes the DAX ...

ARTICLE

Stairway to PowerPivot and DAX - Level 7: Function / Iterator Function Pairs: The DAX AVERAGE() and AVERAGEX() Functions

Business Intelligence architect, Analysis Services Maestro, and author Bill Pearson explores the DAX...

ARTICLE

Stairway to PowerPivot and DAX - Level 1: Getting Started with PowerPivot and DAX

Learning DAX is easy once you install the PowerPivot for Excel 2010 add-in and get practical exposur...

Tags
dax    
powerpivot    
stairway series    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones