I’m a big fan of Greg Deckler’s work, and on Friday he posted an incredibly interesting and thought-provoking piece entitled “CALCUHATE - Why I Don’t Use DAX’s CALCULATE Function”.
I initially thought this was a joke, since I couldn’t imagine how you could do anything useful in DAX without CALCULATE, but after reading through this twice now, I think he makes some very compelling points. Having just spent the better part of two days debugging a really complex report with layers and layers of branched measures, his points really resonated with me. I’m not ready to throw CALCULATE on the scrap heap just yet, but this post definitely got my wheels turning.
Will be very interested to hear what you all think.
interestingly, I heard something about this when going through the recent Guy In a Cube videos (I think it was one of the live streams) - this had Adam scratching his head, because he said that his understanding is that even if you don’t include CALCULATE in your written measure - the DAX engine is adding it at the calculation level regardless.
So you may not type it in yourself, and you may never even use it - but from what Adam said, even a simple measure like SUM( Usage[Sales] ) uses CALCULATE.
I’ll be interested in watching to see what some others have to say about this.
Personally, I think it’s similar to the path I followed in Excel - once I learned how to use INDEX/MATCH - I tried to replace every use of VLOOKUP with the new formula combo I’d learned. Until I realized that I was writing more complicated formulas in some places where a simple VLOOKUP was just fine.
I did the same thing at first with Pivot Tables, and with countless other things. It was a great way to be sure I understood the formula/concept inside out - but it also made my work more complex for others at times.
I finally adopted a mind-set that has really helped me a lot - just because I have a new hammer, not every problem is a nail (and trying to hammer a bolt into place is not fun). I look at the problem at hand, and the tools I have, and see what is the best choice for the job, not ‘my choice’ because of the shiny new tool I found. But, that’s me
Definitely an interesting take, though I don’t share the same sentiment. I picked up his book not too long ago and after the first couple of pages I put it down and never picked it up again. His way of doing this total put me in state of unease because it’s in stark contrast on how I have been doing things. If i could return it I would, but guess I’m stuck with it.
Sure, again, that’s all great in theory. In reality, just exactly how often does DAX performance come up anyway? I would say not very often at all. Probably 99.9% of the time, DAX performance isn’t an issue. Meaning that for every 1000 DAX calculations you create you might hit a serious performance issue once. So, in that one case, sure, knock yourself out and use CALCULATE if you can.
I don’t buy that in the least. I dont know, maybe more people than I realize are doing things in PBI with not that much date or basic things?! But I know for me I have run into queries being slow and moving things from FILTER to CALCULATE has been a life-savior. Perhaps I’m in the minority and really like getting into nitty-gritty of the DAX Storage Engine and Formula engine and seeing how things really work.
I do agree with him on variables though. I think they really do help with performance/readability/maintenance, but you cannot use variables exclusively at all times. A measure stored in a variable and a measure explicitly laid out can have different meanings when they are used, think context transition in an iterator.
He is using calculate in the above though, but it’s just not explicitly there- but it’s still there. For me, it seems a little harsh and misguideded for others that read that blog post to demonize CALCULATE. Do people use it incorrectly or without really knowing what’s going on? Sure! but the same thing can be said for every function in DAX/PQ/Excel/Etc. I wouldnt disregard any function, especially one as valuable as calculate.
Just like @Nick_M, I don’t share the same sentiment. I like a bunch of the work he has done in the past, but I’m such a believer in CALCULATE, I’ll have to agree to disagree with him on this post.
Unfortunately, I think he wrote this starting with a clickbait-y title and a slightly trollish style that obscured what I think is the real gem here:
“Using variables allows you to segment complex calculations into discreet, easily testable chunks of code that are self contained and fit a more natural, top down coding style. When using variables, if you need to test if a particular piece of code is being calculated correctly, you simply edit your RETURN statement to return the variable that is calculating that piece of code. Easy peasy. Furthermore, you can solve a problem in a non-linear and flexible way. With nested DAX, you need to follow an extremely rigid and linear path from A to B. And that’s just not very enjoyable or, in my opinion, efficient. Third, when I go back to troubleshoot the code after a few months, all the code is in one spot, I do not need to go hunting and searching for dozens of different, separate measures to track down bits and pieces of code.”
I certainly don’t take this as an absolute in the way he does, but I do think that for very complex reports with lots of nested/branch measures, it may be a construct that provides better transparency and easier debugging and maintenance.
@Nick_M - I picked up his book not too long ago and after the first couple of pages I put it down and never picked it up again. If i could return it I would, but guess I’m stuck with it.
Agree to disagree on this one. For me, it’s my most used DAX book other than the Definitive Guide, and I love his unorthodox, creative thinking in it. It pains me to think of it going unopened on a shelf, so let me propose a win-win. I’ll take it off your hands at cost, and offer it up to the winner of eDNA Data Challenge #5. {Just to clarify, we are talking about DAX Cookbook, right?}
It’s something I haven’t ever thought too deeply about. But you probably can use CALCULATE a bit less and have no problem.
I have learnt DAX a certain way so I’m not going to go and change up what comes naturally but it does highlight there are many ways to solving you’re challenges in Power BI
I don’t agree with this though
In the second case, using measures as a substitute for variables devolves into a maintenance nightmare. One it muddles up the data model. There are lots of instances where you never actually have to reuse a measure somewhere else so having it hanging out in the data model is a waste. Two, it means the DAX code is spread out all over creation. So if you are trying to debug the code it is not all in once place, you have to start at one DAX calculation and then trace everything back to nested upon nested upon nested DAX measures. I’ve done it, it’s truly an awful experience.
In my view there is still no better way in Power BI to organize your measures than in measure groups and use measure branching techniques whereever possible.
I think some people/educators forget that the most people using Power BI or starting to learn it, probably don’t even know what variables mean or what debugging means. You’ve got to start with the right baby steps and work up from there.
Nested measures or measure branching is actually the easiest way to debug things especially if you build up a large formula. So I don’t get that part of it.
I know not everyone out there agrees with me on this, but in terms of learning DAX and getting best practices into your models as you learn its still the superior way to manage things in Power BI.
…and use measure branching techniques wherever possible.
I think some people/educators forget that the most people using Power BI or starting to learn it, probably don’t even know what variables mean or what debugging means. You’ve got to start with the right baby steps and work up from there.
Again, 100% agree. If you don’t start by developing a solid foundation, you’re going to struggle, and I think measure branching is the best foundational approach to learn and build upon.
Nested measures or measure branching is actually the easiest way to debug things especially if you build up a large formula. So I don’t get that part of it.
This is where I’m getting sold on Deckler’s approach (but mainly only for complex measures). For those situations, I do think that having all the inputs in one measure where you can sequentially isolate problems simply by changing the variable in the RETURN statement has a lot of merit. Otherwise, you’re trying to debug multiple branched measures at the same time and work through potentially complex context interactions, which I find difficult if the branching reaches back 3-4 levels deep.
I think he’s absolutely right that the difference “really represents two wildly different programming approaches and philosophies around DAX coding”
Both approaches are clearly workable and a matter of preference and style. The thing I found really valuable and eye-opening in the article was that I didn’t realize that this second valid approach even existed. I’ve always just done measure branching and assumed any other approach was just bad DAX…
I do think the article served its intended purpose of generating good discussions.
I’ve found for beginner users the best way to ‘debug’ measures is to place each step along the way into a table so you can see all the numbers and calcs together. For this you still need the measure branching technique in a lot of cases, more probably.
I still do it this way today. It’s worked for me so I’m not going to change it.
This whole discussion is exactly the same as with excel/vba. There’s many ways to do the same thing and that is all good. Whatever is your preference.
Saying you should only do something one way is like telling a musician there’s only one way to play an instrument! There’s a lot of parallels here with learning an instrument if you think about it. You’re methods probably evolve over time.
