skip to Main Content

You Had Me at Calculate

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.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

Leave a Comment or Question