The Case for No CALCULATE

The following is something that I have specifically avoided writing for quite some time. Why? Well, because at its heart, the subject of how to “properly” write DAX is effectively a technological debate. And technological debates, by and large, tend to be tedious, unwinnable affairs that put most of the general population to sleep.

Further, be it Windows vs. Linux, Apple vs. Android, whether open source is bad or good, or whether Python is a garbage language or not, statements of facts and logical reasoning largely do not matter in technological debates. Individuals on either side of these debates tend to have generally already made up their minds regarding the topic at hand and not even an act of God or Charles Babbage rising from the grave to weigh in on the matter will dissuade them from their opinion. Hence, engaging in technological debates is ultimately useless and a huge waste of time and effort. Which is why I’ve generally avoided this discussion.

Certain recent events, however, have conspired to make me take another look at this topic and, ultimately, pen the article you are reading now. Namely, the introduction of DAX Optimizer, which seems hell bent on characterizing every calculation involving an iterator and/or not involving the CALCULATE function as an “anti-pattern” as well as what seems like a growing intolerance within certain circles for any dissenting opinions on how to “properly” write DAX.

Now, I write this knowing full well that what you are about to read is ultimately a waste of time. No matter how artfully I present my viewpoint and opinion, the odds of anyone’s mind being changed on this subject are beyond miniscule. But that’s OK, because I have a strong opinion on this topic, and I want my viewpoint to be on record in my own words. Given that I, in many ways, unwittingly inspired the No CALCULATE approach to DAX, perhaps I feel obligated to present the formal case for the approach. Or maybe I just can’t keep my mouth shut and feel like popping off for no good reason whatsoever. Either way, let’s get into it.

At the heart of this debate are two rather polar opposite approaches to writing DAX code within Power BI. On the one hand, you have the old school approach perhaps most notably championed by Marco Russo and Alberto Ferrari of sqlbi.com and The Definitive Guide to DAX fame. The approach essentially relies on building star schemas and features the CALCULATE function front and center. Given that those two individuals literally wrote the book on DAX, many herald this approach as the only “proper” way to write DAX.

The other approach is the “No CALCULATE” approach. As one might expect, this approach eschews the use of the CALCULATE function, which; also unsurprisingly, basically makes it heretical to disciples of the aforementioned approach that features CALCULATE.

Let’s look at an example of both approaches using something simple like a running total. Using CALCULATE, a running total often looks like the following:

CALCULATE(
? ? SUM('Table'[Value]),
? ? FILTER(
? ? ? ? ALLSELECTED('Dates'[Date]),
? ? ? ? ISONORAFTER('Dates'[Date], MAX('Dates'[Date]), DESC)
? ? )
)        

Conversely, the No CALCULATE approach generally looks like this:

? ? VAR __Date = MAX('Table'[Date])
? ? VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
    VAR __Result = SUMX(__Table,[Value])
RETURN
? ? __Result        

In looking at these two approaches you will notice significant differences even though both include the same three functions, MAX, FILTER and ALLSELECTED. Beyond those three functions, however, the approaches are quite different. In the CALCULATE approach, the CALCULATE function serves as a “container” of sorts for the entire calculation and you kind of have to read the formula from the inside-out. The calculation also involves, and in fact requires, two tables, a date dimension table (‘Dates’) and a fact table (‘Table’).

Conversely, the No CALCULATE approach heavily features variables (VAR statements) including a virtual table (__Table). In addition, only a single fact table is required (‘Table’). Another hallmark of the No CALCULATE approach is the minimization of DAX functions used. Instead of ISONORAFTER, a simple <= is used. Minimizing the number of DAX functions used makes the entire approach easier to learn which is kind of the entire concept for No CALCULATE to begin with.

DAX “purists” will likely make the argument that the first approach, I’ll call it the “traditional” approach, is the correct approach because “CALCULATE has always been central to DAX”. While true, one must reconcile the fact that DAX has evolved over time. Early DAX did not include the concept of variables. Variables were introduced to DAX in 2015. Functions like EARLIER, EARLIEST, and; in my humble opinion, CALCULATE, are all vestiges of non-variable DAX. While it may have been possible in the pre-variable era to write DAX without CALCULATE, some key No CALCULATE benefits such as top-down coding and ease of debugging simply aren’t possible without variables.

Now, my purpose with this article is not to argue which approach is “right” and “wrong”. Furthermore, my purpose isn’t really to espouse the virtues or disadvantages of either approach. One could argue that the No CALCULATE approach follows a more logical flow, is easier to debug, and works for both single table data models as well as star schema and snowflake schema data models. One could further point out that the traditional CALCULATE method actually does NOT work with single table data models.

But, as we already know, facts and logical reasoning are effectively useless when it comes to technological debates so who cares? Thus, my real purpose with this article is to demonstrate that both approaches are valid approaches and, in fact, are intended for two very different personas. To do that, we need to review a bit of history. Not much, just a little. It will be painless and quick, I assure you.

So, prior to business intelligence tools like Power BI, business intelligence was carried out within organizations by two different types of personas using different sets of tools. On the one hand you had your information technology (IT) “professionals” who largely worked in database software like SQL Server. On the other hand, you had business users and analysts who largely worked in Excel.

The IT crowd was largely dismissive and derisive of business users and their “toys” such as Excel. Conversely, the business users were equally dismissive and derisive of IT because everything took too long. Ask IT for a report and you might get something back in six months to a year. Useless when you need it for tomorrow’s meeting.

Now, this state of affairs went on for decades. Each persona equally dismissive and derisive of the other. But, as long as each persona stayed within their own lane, there was an uneasy truce. IT forbid business users from monkeying around in their databases and, in turn, IT also turned a blind eye to all of the Excel shenanigans going on. Business users were perfectly happy to not have to rely on IT for every little thing and were equally happy with Excel. Harmony.

Then along came tools like Power BI. Suddenly, both personas were thrust together using the same tool. Power BI exponentially increased the analytical power of Excel for business users, so much so that the IT crowd also considered Power BI a legitimate and powerful tool for building out complex data models consisting of millions of rows of data. And herein lies the origin of the conflict between the “proper” way to write DAX and the No CALCULATE approach. With two diametrically opposed personas using the same tool, is it any wonder that there would emerge two polar opposite ways of approaching DAX? In my opinion, nothing could be less surprising.

The IT crowd took the powerful capabilities of Power BI and did what was familiar to them, built out star schema cubes, a data model structure particularly suited to using CALCULATE (well, let’s be honest, the only data model structure suited to using CALCULATE). Conversely, the business users were familiar with spreadsheets which translated to single table data models, a use case where CALCULATE is largely ineffective and often gives bizarre results.

The problem, in my opinion, is that the discussion around the “proper” way to write DAX has largely been driven by the IT crowd with little or no consideration to how business users utilize the tool. But guess what? Business users far outnumber the IT folks, probably on the order of 10 to 1. Thus, the vast majority of Power BI data models are single table data models, models that simply do not lend themselves to using CALCULATE.

Now, of course, the IT crowd would say that the business users are idiots who just need to learn how to use the tool “properly” by building out star schemas. And the business users, well they still think that the IT folks are idiots because they can’t provide answers without overly complicating things and taking forever to; well, to do anything at all. So who is right? Ultimately it doesn’t matter. The fact remains that IT folks are going to do what IT folks do and business users are going to do what business users do and that’s simply not going to change. Ever.

I suppose I could just leave it at that. The two approaches are both valid, they are just geared toward two different personas using the same tool. But that’s just not who I am. And even though I realize that what I am about to say is a complete and utter waste of time and effort, let me leave you with this. The No CALCULATE approach to DAX is far superior to the traditional CALCULATE approach. Why? Well let’s do the list:

• Works with 100% of data models
• Is far easier to learn with a far lower barrier to entry
• Follows a more logical, top-down programming flow
• Is easier (as in actually possible) to debug
• Is remarkably consistent, every calculation is basically a few variables, a filtered table and an iterator function
• Sets you up perfectly for ditching DAX Time Intelligence functions
• Is just as performant, if not more so, than traditional CALCULATE approaches (see Brian Julius’ excellent work on this subject)
• Has survived every “No CALCULATE Challenge” to date ( 1 and 2 )
• If you find a situation where CALCULATE is useful, you can still use it. This isn’t like being vegan or Amish or getting your man card taken away for wearing a fanny pack. Nobody is going to shame you for slipping in a CALCULATE or CALCULATETABLE statement. I can assure you, however, that the reverse of that statement is absolutely not the case. Zealots gotta zeal after all.

Or, you can waste your life learning the traditional, old school CALCULATE approach and have it only be applicable to probably less than 20% of all Power BI data models out in the wild. Which, of course, is what you are going to do. Why? Because facts and logical reasoning don’t matter in a technological debate. That’s why. I mean, there are people that still have land lines after all. So, it’s up to you whether you want to cling to traditional, antiquated ways of doing things that utterly ignore advances in technology. You do you, I make no judgements.

However, on the off chance I actually convinced anyone or so much as piqued someone’s interest, you might find my article CALCUHATE – Why I Don’t Use DAX’s CALCULATE Function interesting. Or maybe check out my YouTube channel DAX For Humans which seeks to teach the DAX No CALCULATE approach. And if you are looking for “The Definitive Guide to No CALCULATE”, check out my book, DAX Cookbook, as it is effectively a treatise on the subject.

I would like to thank Brian Julius for his input on this article and all of his support for the No CALCULATE approach. He is the guy that actually coined and popularized the term “No CALCULATE” after all. I just sort of got dragged along for the ride…