PowerPivotPro

“I will start with the calculate curry.  And a green tea.”

-Wise first time DAX customer

Is There a Preferred Way To Learn DAX Measures?

I’m glad you asked   Actually, I do get asked this quite often – in email, on Twitter, and in person at events like last week’s PASS Summit in Seattle.  I promised the folks at my Birds of a Feather lunch table that I would blog about the approach that I take in my consulting/training engagements.

I then, of course, promptly forgot.  Good thing Phil reminded me today in Twitter, because it gave me an opportunity to put together that menu mockup above.  Which clearly is where my time is best spent while watching Monday Night Football.

So, here it is.  The official PowerPivotPro technique, refined by decades spent in the trenches of DAX.  But first, some background.

Sometimes, You Actually CAN Have Too Much of a Good Thing

If you read my post on the official Excel blog, you may remember me saying something to the effect that DAX measures are a feature so rich, it’s as if Excel had a whole team working on them for a decade, then delivered it all at once in a single release.

That’s obviously a good thing, but it also comes with a downside:  it’s a lot to digest all at once.  Most products give you the simple stuff in v1, then add some more power in v2, continue in v3, etc. – and the user base gets to grow their knowledge slowly over time.

Not so in PowerPivot v1.  We’re basically looking at a version 5 product when it comes to DAX measures and all of the functions supported.  So it’s a bit daunting – of all these many functions, which ones would have been released in v1, had we not skipped straight to v5? 

Because, hey, those are the ones we want to learn first.  But, they are hiding in there next to functions from v2 – v5, and the function names provide little clue as to complexity.

The Spicy Scale to the Rescue!

On Thursday night we had a very entertaining dinner with members of the PowerPivot/SSAS team.  At my table, we discussed the idea of a spiciness scale for DAX functions, to help people approach them in the right order.  It would be just like the spicy scale you see on menus at Chinese and Thai restaurants.

(I was suggesting the idea as something that might be useful in the product itself – I now have the luxury of just throwing out ideas at random, and my former colleagues decide whether it actually makes sense in the real world…  in a sea of ideas coming from all directions.  Their job is a LOT harder.)

Some amazing people in those pictures, including, but not limited to, people who get mentioned here all the time – Kasper (now on the PowerPivot team), Marco and Alberto, Marius of DAX Superhero fame…  and that is leaving out some of my all-time favorite people.

OK, let’s dive in.

Spice Level One:  Fundamentals, COUNTROWS, and DISTINCT

Before you get rolling, it is very important to understand how measures actually work.  And while you’re at it, it helps to have a couple of new (but simple) functions to try out.

This explanation works best in video form, so I’ll direct you to my YouTube video on the topic.

Spice Level Two:  CALCULATE

Now that you understand the basics of pivot cell context and how those filters apply to measures, it’s time to start overriding and modifying that filter context.

CALCULATE is the foundation for a whole world of magic, and it’s a lot like SUMIF, which is excellent news for Excel pros.

You can learn more about CALCULATE here, here, and here.

Also note that you don’t have to restrict yourself to the = operator here.  You can use <, >, <=, >=, and <> as well.

Spice Level Three:  ALL

Once you get comfortable setting fields to single values, the next expansion of your powers is removing filters altogether.

ALL is what you need.

Spice Level Four and Beyond:  “It Depends”

As I’ve mentioned before, the best way to learn is with your own data, rather than textbook cases.  So I would let that be your guide.

Many people will probably want to experiment with time intelligence functions next, employing them to change date filter context in much the same way that ALL does…  except with more specific, time-shifting results.  I really should do a new series of posts just on time intelligence functions.

Or alternatively, do you want a measure that iterates over values that are NOT shown in your pivot?  Or do you want a measure to be a MAX at the lowest level of the pivot but then a SUM at higher levels?  If so, I recommend you try SUMX next.

Or maybe you want to revisit CALCULATE, and instead of setting fields to particular values, or even comparing then to particular values using <, >, <=, >=, or <>, let’s say you want to compare those fields to *measure* values?  Well, you are ready for FILTER.  Sort of

I will probably return to this subject in the future, time allowing, but for now, this is the gist of it.  If you master levels 1-3, you have a tremendous amount of new power at your fingertips, and you are ready for new things, but it’s hard to cover any of those new things in a short paragraph or even a single link.  I usually spend several hours on the level four stuff with my consulting/training clients, as a point of reference (and that is with the comfort of the clients’ own data and business problems).

Revisiting ALL()

Yes, I’ve used ALL() a number of times already.  But I’ve never covered it directly – it’s always been a side topic to something else.  So I thought it was time I went back and made sure I had a specific post on it.

The short version is: ALL() removes filters from specific fields during a measure calculation.  It “De Niros” your measure… but in a controlled manner.

Syntax

    ALL( table_or_column, [column1], [column2], etc.)

So the first parameter is required, and can either be a single column or an entire table.

After that, you can specify as many other columns as you’d like…  as long as the first parameter didn’t specify a table. If your first  param is a table, you can’t specify additional columns.

Also note that even when specifying columns, the ALL() function requires you to always include the table name.

Lastly, all columns listed must be from the same table.

Legal Examples:

1. ALL(Table1)
2. ALL(Table1[Column1], Table1[Column2])

Illegal Examples:

1. ALL([Column1])
2. ALL(Table1, Table1[Column3])
3. ALL(Sales[Column1], Customer[Column2])

When to use ALL() – Example #1

You never use ALL() by itself.  You always use it in the context of another function.  It’s all about setting context for a calculation.  It is not a calculation unto itself.

The simplest place to use ALL is in a measure that always shows you a grand total of something, regardless of what filters are in play.  Revisiting the Temperature Mashup demo for a moment, here’s a pivot sliced by Temperature, showing a normal Sum of Order Quantity:

 
Now I add a measure that sets the Temperature table to ALL:

    [AllTempsQty] = CALCULATE(
                      SUM(Sales[OrderQuantity]), 
                        ALL(Temperature)
                    ) 

That ends up looking like:

(If you need a refresher on CALCULATE, which is probably the most useful function in all of DAX, check out this post.)

So, on Hot days, we sold a total of 2,245 Accessories.  And overall, across all temperatures, we sold 36,092 Accessories.

But note that the numbers are different across Category for both measures.  That’s because [AllTempsQty] did not use ALL against the Category columns (or table).

Cool.

Example #2 – % of Total

OK, but in many cases, you don’t actually want to see [AllTempsQty] in the pivot.  You just want to compare the filtered version of a measure to the unfiltered version.

So we can define something like:

  [PctOfAllTempsQty] = SUM(Sales[OrderQuantity])  / 
                       CALCULATE(
                          SUM(Sales[OrderQuantity]),
                          ALL(Temperature)
                       )

Which gives us:

Note that I switched Excel’s number formatting for this measure to be Percentage, keeping me from having to include * 100 in my measure.

Of course, if I wanted to keep my [AllTempsQty] measure, then I could have defined my new percent total measure as the following instead:

  [PctOfAllTempsQty] = SUM(Sales[OrderQuantity]) / 
                       [AllTempsQty]
                    

And that would have produced the same results.

Example #3 – Using ALL() in places other than CALCULATE()

If we revisit the post on derived measures, ALL() is an excellent modifier to use against an existing measure.

Let’s take a measure I defined in the Temperature Mashup, the [Qty per Day] measure:

And then we define a new measure:

  [QtyPerDayAllTemps] = [QtyPerDay](ALL(Temperature))
                 

That yields:

You like?  I like

“AGAIN!”

-Pai Mei

SUMX() – the great iterator

Have you ever written an array formula in Excel?  (Don’t worry, most people haven’t).  Have you ever written a FOR loop in a programming language?  (Again, don’t worry, there’s another question coming).  Have you every repeated something over and over again, slowly building up to a final result?

That’s what SUMX() does.  It loops through a list, performs a calc at each step, and then adds up the results of each step.  That’s a pretty simple explanation, but it produces some results in pivots that are nothing short of spectacular.

Anatomy of the function

     SUMX(<Table>, <Expression>)

THE BRIDE: “What praytell, is a five-point palm, exploding function technique?”
BILL: “Quite simply, the deadliest blow in all of the analytical martial arts.”
THE BRIDE: “Did he teach you that?”
BILL: “No. He teaches no one the five-point palm, exploding function technique.”

That’s kinda how I feel about the description of SUMX in the Beta release:  “Returns the sum of an expression evaluated for each row in a table.”  It merely hints at the power within.

Oddly, the best way to show you what I mean is to start with some useless examples and then build up to useful ones.  For all examples, I will use the following simple table, Table1:

 Table1 

Useless Example #1:  By the whole table

     SUMX(Table1, Table1[Qty])

Returns:  35, which is the total of the Qty column.  Might as well just use SUM([Qty]).

Why:  Well, it iterates over every row in Table1, and adds up [Qty] at each step, just like the description says it would.

Useless Example #2:  By a single column

     SUMX(Table1[Product], Table1[Qty])

Returns:  An Error

Why:  Table1[Product] is not a Table, it’s a Column.  And SUMX demands a Table as the first param.

Useless Example #3:  By distinct values of a column, sum another

OK, I’ll wrap the [Product] column in DISCTINT(), since that returns a single-column table:

     SUMX(DISTINCT(Table1[Product]), Table1[Qty])

Returns:  An Error

Why:  [Qty] is not a column in the single-column table DISTINCT([Product]).  Only [Product] is.  Why did I even try this?

That’s where I gave up awhile back.  Until I learned…

Almost-Useful Example:  The Second Param Can Be a Measure!

And even better, that measure CAN access other columns even if you use DISTINCT.  First let’s define a [Sum of Qty] measure:

     [Sum of Qty] = SUM(Table1[Qty])

And then re-try the previous example with the measure, not the column:

     SUMX(DISTINCT(Table1[Product]), [Sum of Qty])

Returns:  35 Yes, the total, again.   But this time, the “Why” is worth paying attention to.

Why:  Let’s step through it.  Remember, for each value of the first param, SUMX evaluates the expression in the second param, and then adds that to its running total.

Step One:  SUMX evaluates DISTINCT([Table1[Product]) which yields a single-column table of the unique values in [Product]:

Step Two:  SUMX then filters the Table1 (not just the [Product] column!) to the first value in its single-column list, [Product] = Apples.

Then it evaluates the  [Sum of Qty] measure against that table, which returns 17.

Steps Three and Four:  The process repeats for Oranges and Pears, which return 13 and 5:

Last Step:  SUMX then adds the three results it obtained:  17, 13, and 5, which yields 35.

A lot of work to get the same result that the [Sum of Qty] measure can get on its own, but now that you know how it operates, let’s do something else.

And now, the Useful Example!

Let’s define another measure, which is the count of unique stores:

     [Count of Stores] = COUNTROWS(DISTINCT(Table1[Store]))

For the overall Table1, that returns 2, because there are only 2 unique stores.

Let’s then use that measure as the second param:

     SUMX(DISTINCT(Table1[Product]), [Count of Stores])

Step One:  same as previous example, get the one-column result from DISTINCT:

Step Two:  filter to Apples, as above:

…and the [Count of Stores] measure evaluates to 2 – 2 unique stores have sold Apples.

Step Three:  Oranges

…again, the measure evaluates to 2.  2 unique stores sold Oranges.

Step Four:  Pears

…hey look, only one unique store sold Pears.  So the measure evaluates to 1 here.

Last Step:  Add them all up.  2 + 2 + 1 = 5.  SUMX returns 5.  This basically means that there are 5 unique combinations of stores and products that they sell.

Why is that useful?

Well, I can’t share the precise case I was working on, because it belongs to a reader’s business.  But trust me, you are going to find yourself wanting this sooner or later.

Things to keep in mind

1. SUMX responds to pivot context just like anything else.  So if you slice down to just a particular year, your results will reflect only what Stores sold in that year.
2. AVERAGEX, MINX, MAXX, and COUNTAX all work the same way.  So if you want to iterate through just like SUMX but apply a different aggregation across all of the steps, you can.  Those would return (5/3), 1, 2, and 3, respectively in our example.
3. The fields referenced in SUMX do NOT have to be present in your pivot view.  In my case, SUMX was working against [Store] and [Product].  But my pivot could just be broken out by [Region] on rows and sliced by [Year], and the measure still works.  (I like to think of it as a stack of invisible cells underneath each pivot cell that you can see, and SUMX is rolling up a lot of logic across those invisible cells to return a simple number to the top cell you can see.)

More to come!

Yeah, I am not even done with SUMX.  Like Jules told you, it’s some serious gourmet DAX

Mmmm! Daaaamn, Jimmie! This is some serious gourmet DAX! Usually, me and Vince would be happy with some freeze-dried Taster’s Choice right, but he springs this serious GOURMET DAX on us!

-Jules Winfield

 
 
 
What I’ve seen so far is great, and yet…

The things I have done with DAX so far are a HUGE boost to what I can do in Excel formulas alone.  Here are some of the functions I have explored to date:

1. RELATED – a faster, simpler VLOOKUP.
2. CALCULATE – the big Kahuna.  A version of SUMIFS that I can use in a pivot measure to remove, alter, or override pivot filters and just aggregate the source rows that I want.
3. ALL – the ability to strip pivot filters from certain fields or tables during a measure calc, enabling very flexible custom ratios when combined with CALCULATE.
4. DISTINCT – returns the set of unique values of a particular column within the current pivot filtering context, very useful (so far) when combined with COUNTROWS.
5. COUNTROWS – no more need for finding the unique-valued column in a current context, you can just count the source rows that meet current filter criteria.

But I’ve seen some things over the past week that made me realize:  I’ve been lax…  about DAX.

I just found a cupboard full of “gourmet DAX”

Funny thing folks:  in many ways, I am learning right alongside of you when it comes to DAX.  When I left Redmond in August, DAX was just starting to appear in the internal builds.  So while I was getting a little bit of an advanced look, in other ways I’ve been watching the DAX movie in progress just like everyone else.

Last week, a reader asked me a series of questions basically saying “can you do this in DAX?”  These were crazy things they wanted to do – VERY useful stuff for them, and totally understandable why they wanted it.  And my initial answer was “um, no, DAX can’t do that.”

I was wrong.  DAX very much CAN do those things.  And many, many other things I had not considered.  I’m a little embarrassed by holding out on you guys, but I’ve been holding out on myself, too.

I mean, I knew I had not yet dived into the time intelligence stuff.  Other people are doing a decent job of that, and my intent was to build up to it, slow and steady.  But I was missing other things, too.

New resolution:  at least two DAX posts a week

This is a long time coming really.  It’s time to devote a much bigger chunk of my efforts toward educating everyone – myself included – on the capabilities and finer points of DAX. 

Here’s a quick preview of what’s in store for us:

1. SUMX() – the FOR loop of DAX, kinda like Array Formulas in Excel
2. CALCULATETABLE() – generate a custom filtered table, on the fly, and then use that as the table argument to another function
3. Using one measure as an input to defining another – self explanatory in some ways, but you won’t believe how often this gets you out of a jam.
4. Using a different formula at one level of a pivot vs. another – just one example of something you can do by combining some of these techniques.

Seriously, I am excited.  I’m like a kid who’s discovered a secret room in the castle.