Monthly Archives: December 2012

57.1 DAX Formulas for PowerPivot – Chapter 15

Two aspects of DAX measures that give them their power are:

  1. Their calculations are independent of particular data values
  2. Their calculations can be applied in any filter context

As you start to do more with DAX measures, however, you find that you need to change calculation behavior based on the above 2 situations. Examples are avoiding division by zero and correctly calculating values for contexts where only one value is present in the filter context, respectively. The functions introduced in Chapter 15 (IF(), BLANK(), ISBLANK(), HASONEVALUE(), SWITCH()) help you craft calculations for these types of situations. In addition, the VALUES() function is introduced. VALUES() is interesting because it can return either a single value (scalar) or multiple values (table) depending on context.

57.1 DAX Formulas for PowerPivot – Chapter 14

Chapter 14 is about time intelligence. If I were Rob and this were a blog post (actually it is a blog post), I might start off something like this:

Ah, Time Intelligence, the cornerstone of every nutritious breakfast...

Time Intelligence. The cornerstone of every nutritious breakfast..

Of course, I’m not Rob so I won’t be doing that. But the point is that time intelligence is fundamental to business intelligence. And chapter 14 (a comparatively large one for this book) introduces time intelligence in DAX.

Time intelligence allows you to analyze data comparatively on various dimensions and time is an important one. Year to date metrics, moving averages, metrics compared to the same period last year, and things like that all use time intelligence functions in their calculations. The chapter starts off by making the point that the time intelligence functions in DAX work with “standard calendar” – the calendar most of us are familiar with. It makes that point that many business scenarios involve non-standard calendars and those will be covered but not in this chapter. It then continues describing how to build a date lookup table and how to configure it so that Excel and the DAX time intelligence functions can use it. Then the various types of time intelligence functions are described.

This is an important chapter. It’s hard to imagine becoming a “DAX master” without having knowledge of how to use time intelligence functions in your “tool belt.”

BTW, on page 130 it looks like there is something missing:

“Let’s create an example measure:
[Total Sales PARALLELPERIOD Back 1 Year] =”

and then no formula for the measure. If you look in the book’s companion files (specifically ch14.xlsx), you can find it in the model:

“=CALCULATE([Total Sales], PARALLELPERIOD(Calendar[Date], -1, Year))”

57.1 DAX Formulas for PowerPivot – Chapter 13

Chapter 13 starts off by finishing the second example started at the end of chapter 12 and, coincidentally, introducing the FILTERI() function. Guidance is given for when to use FILTER() and why it’s cool. Along the way, some tips for debugging measure calculations and instructions for how to do copy-and-paste measure development are presented.

The “show me products above a selected list price” is enhanced to show how to do upper and lower bound thresholds. To make it interesting, the ranges in the example are overlapping.

I feel compelled to point out a couple of errors in the chapter:

  • When creating the disconnected table for the second example, a column is added to the table to facilitate sorting. The name of the table, “MidPt,” implies that the data value will be the midpoint of the range for each row. But the formula doesn’t calculate the mid point – it’s ([MaxPrice] – [MinPrice]) / 2 when it should be [MinPrice] + (([MaxPrice] – [MinPrice]) / 2).
  • At the top of page 109, the modified version of the measure for the Sales table is not shown (as expected). Instead, it’s just a copy of the original measure as shown on the previous page.

Hey Rob, if you’re looking for someone to review your next book before publishing, give me a call 🙂

57.1 DAX Formulas for PowerPivot – Chapter 12

Chapter 12 shows how to build a disconnected table (one without a relationship to other tables) that can be used to provide values to other calculations. You can insert a slicer based on the disconnected table to allow report users to select values from the table, like parameters.

The chapter provides 1.5 examples of using this technique. The first one shows how to select a USD/EUR exchange rate and see how sales would be affected. The second example starts to show how to use a “slicer parameter” to set the minimum list price of products to show in a PivotTable. This example is not finished in Chapter 12 because, in order for the measure behind the calculation to work, we must use the FILTER() function in the calculation. And that’s the topic of the next chapter.

57.1 DAX Formulas for PowerPivot – Chapter 10

Up until chapter 10, all the examples in the book were based on a single table, a sales table. Chapter 10 is about working with multiple tables that are joined together by relationships.

The chapter makes that point that, with PowerPivot, you don’t have to go through efforts to bring all your data into a single table to work with it and, in fact, it’s better if you don’t. A distinction is made between the table with the data in it (called the Data Table) and related tables that contain attributes that you want to use for analysis (called Lookup Tables). An example is given that shows how filter context flows across relationships. At the end of the chapter, Rob updates his diagram of how PowerPivot calculates values shown in the cells of a PivotTable. This model is key to understanding how to write effective DAX measures.

In addition to chapter 10, I also read through chapter 11 which is only half a page. In chapter 11, Rob makes the point that if you’ve made it this far you have already gained the skills to “improve the quantity and quality of insights you can deliver by 4-5x” and you have now gained the same amount of material as a full day of intensive training in one of Rob’s onsite, personalized courses.

57.1 DAX Formulas for PowerPivot – Chapter 9

We were introduced to the ALL() function in the previous chapter. This chapter goes into more details on ALL() and its siblings, ALLEXCEPT() and ALLSELECTED().

As mentioned in the last chapter, ALL() can be used in the filter arguments of the CALCULATE() function to clear the currently applied filters from the table or columns passed to the ALL() function. When you pass a table as the argument, filters will be cleared for all columns in the table. When you pass individual column arguments, all columns must belong to the same table. Two examples are given for using the ALL() function: percentage of parent and showing the percentage sales based on products selected in a slicer.

ALL() has a couple of related functions: ALLEXCEPT(), handy when you want to remove filters from almost all the columns of a table; and ALLSELECTED(), which keeps all filters except row and column filters. An example of ALLSELECTED()  is shown that allows ALL() functionality but filtered by a slicer control. This is kind of like visual totals for those who know SSAS multidimensional modeling.

57.1 DAX Formulas for PowerPivot – Chapter 8

I missed reading a chapter yesterday. I guess that will happen when you get a new toy

Anyway, on to chapter 8. Chapter 8 introduces the DAX CALCULATE() function. I’ve heard it said that if you understand CALCULATE() you can call yourself a DAX programmer.

The chapter starts off by calling CALCULATE() a “supercharged SUMIF().” I’m not coming to PowerPivot as an Excel expert so that didn’t mean a lot to me (sort of like how I didn’t get how relationships were much better than VLOOKUP right away). Rob starts with a simple example that shows how CALCULATE() works. Then he describes what happens when results are evaluated by the PowerPivot calculation engine. Grokking this stuff is really how you know how to build the calculations you need to do the analysis you want to do.

Continuing on, the chapter shows two useful examples of using CALCULATE() – analyzing transactions of a certain type and growth since inception. The chapter concludes with an explanation of how multiple filters in a CALCULATE() call behave and introduces the ALL() function which allows you to craft a filter that removes all the other filters. The last point the chapter makes is that you really need to understand the context of the cells that contain the data you are analyzing. Just because of the position of numbers in a PivotTable doesn’t mean that the numbers in a column will add up to the grand total of that column.