DAX: I’ve been shortchanging you

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.