I have no idea if this is really how it went down, but as I imagine the creators of DAX sitting around a board (ping pong?) table making design decisions, I see two camps evolving…sort of the Alexander Hamilton/Thomas Jefferson divide reenacted.
I imagine the minimalists, who advocated that the 80 million people who live in Excel would feel right at home with Excel-look-alike code (SUM, MAX, MIN, AVERAGE, LOOKUPVALUE functions). The other camp—we’ll call them the transparency advocates—would point out that it was an artificial seduction of sorts.
To make people think DAX was simply an extension of Excel, that all you need is to learn a few more functions and you are set, is downright misleading.
I call the first camp minimalists because the simpler functions are “syntax sugar.” They collapse the longer code, look more elegant and, in some cases, optimize the code. But they hide what the code is really doing, and that turns out to be very important.
In my imagined scenario, the minimalists won the day, and rightly so. It’s marketable and there is a lot you can do with very simple DAX. Had it not been for the work of Marco Russo and Alberto Ferrari, what’s behind the code would have been very difficult to figure out.
Brian Grant here at CSG Pro would have been in the transparency camp. Or, because he is a market-savvy fellow, he would have gone along with minimalism but argued for transparency in teaching. Recently Brian masterminded our Elements of DAX video series, which is releasing soon.
Each of our videos is saturated with what we call “Long Form” DAX. It eschews all syntax sugar. It exposes every iterator in every function. It’s long, it’s ugly, and instinctively users will shy from it.
Other than reading the Definitive Guide, the practice of writing DAX is the single most important thing a beginning DAX developer can do. Let’s look at an example in short form and long form, so you can understand the differences and the benefits of one versus the other.
Short Form vs Long Form DAX Tutorial
Here is the long form code for Revenue for Lunch and To Go orders from a simple flat table of orders by type and shift.
You have three iterators—SUMX and two FILTER iterators. If you understand what an iterator does, which is critical to the DAX Elements method, you can see exactly what is going on.
The two FILTER iterators go row-by-row over the table for both Shift and Type. They find every row that has Lunch as a Shift, and To Go as a type, create a temp column that gets marked as TRUE, and CALCULATE keeps those rows. SUMX iterates over the newly filtered table and does its calculation.
Now look at the Short Form version, assuming the [Tot Sales] is a pre-created measure. No FILTER, no ALL, simple and clean and elegant.
The only problem is, you have no idea what is going on under the hood. If you know that CALCULATE() is used to apply filters, you know that you will get a filtered version of the [Tot Sales] measure. If you are new to DAX, however, you would have no idea that iterators are underneath all of this.
Why is this so important? If someone asks you for a tricky KPI or metric—one you can’t Google—your best chance of figuring out how to write it is this: Imagine how you’d get the answer if you had to build it in Excel.
Nine times out of ten you will create a copy of the original data, filter it and add some formula columns that you will aggregate. You will iterate by instinct. It’s what people have done since the invention of columns and rows.
If you write long form, every iterator will be transparent. If you understand every iterator, you have got a much better chance of understanding what the DAX is doing.
As you embark on your DAX journey, taking the additional time to write in long form will pay dividends down the line. If you understand what DAX is doing under the hood, you have a much better chance of writing bug-free measures. Or at least measures you can debug. And that is worth every indent and seemingly repetitive line of code that you will ever have to write.
___________
New to DAX development or need to brush up on your skills? Our Elements of DAX video series is coming soon to help you on your DAX journey. Check out this video series teaser so you know what to expect…
