PowerPivot DAX Language Gotchas

Recently I was working on a handout for the International Shelby Conference coming up in June. The topic for the session is PowerPivot, a new way of accessing data to underlay PivotTables and PivotCharts. I have used PowerPivot very occasionally since its release a few years ago, but I had never utilized the "Data Analysis Expression" language, aka DAX, that is part of the tool. DAX is a formula language akin to the formula language that is part of a basic Excel worksheet. But it has many new functions that are designed to apply to entire tables and to columns of tables, rather than just to individual cells and groups of cells. I wanted to include a couple of examples of the DAX language in my presentation, so I dove in. Almost immediately I hit some snags and got errors and unexpected results. I eventually worked through the issues I was having, and I learned some things about DAX in the process. So here is a summary of what I learned in my first foray into the world of DAX.

• There are two places to create DAX-calculated output for the PivotTable: calculated columns added to the source tables in the PowerPivot window and measures added directly to the PivotTable in the Excel window. 

The first one I knew about already. Whenever you load a table into the PowerPivot window, there is an "Add New Column" spot to the right of the existing columns in the table. Just give the column a name and add the DAX formula, and off you go. The new column will act like a native column of data whenever you go to the PivotTable (or PivotChart) report. Where this was a "gotcha" was my lack of awareness of measures. I was frustrated for awhile when I was trying to use DAX formulas in calculated columns that only work properly as measures.

Some of the most powerful analysis solutions using DAX are in measures, not in calculated columns. This is because the formulas in measures can transcend the natural "filters" of the PivotTable to show data beside or beyond the context of the position in the PivotTable. As one example, the PREVIOUSYEAR() function in DAX can allow a cell in a PivotTable to show data from the year prior to the one active for a given cell. Although the same data can be achieved through the design of the PivotTable, with DAX measures the user has more control over how the data will appear in the results.

• There are no explicit conversion functions in DAX.

If the system cannot make an implicit conversion of the data, you're stuck. This is a key bit of information to keep in mind as you think about calculated columns and measures. If you want to do some kind of manipulation of the data that requires a particular data type, make sure the underlying data in the source tables are of the correct type before you get to the DAX context. This probably means using a SQL query to "clean" the data in a view before you import it into PowerPivot.

• The filter parameters used with some DAX functions can implicitly "ignore" the context defined by row and column position inside the PivotTable, but they cannot implicitly ignore the context defined by slicers or report filter selections. 

This was the biggest "gotcha" for me once I started trying to define measures on my own. For an example, suppose I have a PivotTable with no slicers or report filters. This table has rows grouped by CalendarYear. I then define a measure that uses the PREVIOUSYEAR() function without any special filter parameter. The regular SUM() for each row will show the aggregate for the CalendarYear value of that row and the measure will show the SUM() for the prior CalendarYear value. On the other hand, suppose I have a PivotTable with rows grouped by CalendarYear AND a slicer to allow the user to select one or more CalendarYear values. As soon as the user selects one particular CalendarYear value, the measure with PREVIOUSYEAR() will be blank. The slicer choice filters the data in such a way that the PREVIOUSYEAR() function has no data to work with in that context. The only way around this is to add an ALL() filter parameter to the PREVIOUSYEAR() function. By adding ALL(CalendarYear) to the function's filter parameters, the measure is able to explicitly ignore the CalendarYear slicer filter and locate the rows of data it needs for the measure to work.

• There is no NULL in DAX. 

Instead, DAX identifies NULL, empty, and 0 values all as "blank" values, represented in the DAX language as BLANK(). Instead of using ISNULL() to determine if a value is null, as you would in VisualBasic, the command in DAX is ISBLANK().

As I use PowerPivot and DAX more in the future, I'm sure I'll run across more gotchas and tips, and I'll share them in my blog. If you have a tip to share, please share it in a comment on this post. We'll learn together!

GLAcct Table Gotcha

Assumptions are the bane of any query project, and one assumption I recently made bit me today. I had assumed that only Income and Expense type accounts would have any value stored for the ClosingAcctNu column in the GLAcct table. After all, only those two types of accounts use a closing account. In the software they are the only kinds of accounts that allow you to see or to enter any value for closing account information.

I was wrong. Somehow the ClosingAcctNu column in a customer's data was populated with account values for header accounts, total line accounts, and possibly others as well. This was not a conversion issue; these were brand new accounts in a brand new chart of accounts. I had to adjust my query for this customer in order to specifically restrict the search for closing accounts to the detail account type, in order to avoid picking up erroneous closing account information from the Header and Total Line rows in the GLAcct table.

This was particularly frustrating because it only became apparent in the "real world" environment; in my test database there are no extraneous closing account values in the GLAcct table.

Whenever you get unexpected results, looking for the culprit can be tricky. Just be prepared for the unexpected to sometimes be lurking even in a query of familiar tables.

Registrations - Key Information

I worked today for almost two hours with a customer in California who was seeing unexpected information in a query I had written for them some time ago. There were three things wrong with the results, and I learned a lesson from each of them when I discovered the ways to correct them.

First, the query was showing incorrect Custom Field values. After double-checking that the Registrations attendee information was displaying the expected values, I started digging through the query and the tables to figure out what was going on. I spent almost 2/3 of the entire time looking for the cause of this problem. To make the long story short, what I discovered is that the [RGRegCatIndCust].Counter column is not a truly unique Primary Key column. I found that the same Counter value had been used for two different attendees' custom information answers in two different events. The information that was showing up in my query results were for the wrong attendee at the wrong event. In order to correct this problem I had to use both [RGRegCatIndCust].Counter and [RGRegCatIndCustSetup].RGEveCounter as limiters on the custom field values. Once I did that, the query displayed the expected values.

Second, the query was showing the same registrant twice. This was because the attendee had been assigned to the wrong Category at first, and when the attendee information was moved to the correct Category, the amount owed changed. A new invoice had been created to adjust the amount owed. Since my query was grouping by invoice, the second invoice created a second entry in the results. I had to go in and remove the grouping by invoice and then add aggregate functions to the SELECT column information that dealt with invoice info. Once that was done, the correct balances displayed. I learned to always use an in-line view for the Registrations invoice information, because of the way that I used GROUP BY to pivot the custom answers into columns.

Third, the query was returning the wrong number of results. It was 11 shy of the correct number of attendees. I found 10 of the missing 11 by using LEFT joins to the invoice-related tables instead of INNER joins to them. When an attendee had no balance due for registering, there was no invoice created, and therefore the INNER joins were eliminating those attendees. The final 1 person was trickier. In order to get the last person on the results, I had to re-do the FROM clause so that [RGRegCatInd] (the Attendee table) was at the root, making sure to do LEFT joins to all the subsequent tables. That was I was sure to get at least one row for each attendee. What I discovered after I made the change was that one person had registered via WebView and that somehow in the synchronization process the Registrant information was lost but the Attendee information remained. Therefore my original approach of using RGReg (the Registrant table) as the root table omitted the one person whose Registrant information was lost. After I got the final missing person's row in the results, I could readily see that the Registrant information was null.

It was a productive two hours, and I learned some tips that I will apply to all my future Registrations queries. And I will probably need to go back and change some of my prior queries as well.