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!
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:
- RELATED – a faster, simpler VLOOKUP.
- 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.
- 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.
- 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.
- 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:
- SUMX() – the FOR loop of DAX, kinda like Array Formulas in Excel
- CALCULATETABLE() – generate a custom filtered table, on the fly, and then use that as the table argument to another function
- 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.
- 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.