- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Introduction
OK, first off, I don't hate, hate the CALCULATE function. At least not to the same level that I despise the time "intelligence" functions. I mean, I realize that I have made reference to DAX's time "intelligence" functions being one of the four horsemen of the apocalypse but just so we are 100% clear, I truly despise them. But that's for another blog article.
So, I do not hate DAX's CALCULATE to anywhere near that level but it made for a catchy title for a blog post. In reality, I honestly just don't find myself thinking about CALCULATE most of the time any longer. Over the 5 or 6 years that I have been coding DAX I have simply developed a coding style and technique where CALCULATE is just generally useless. So let me explain how I got there and then I would love to hear everyone else's thoughts on this subject because I think it is an interesting topic with a lot of far reaching implications. In reality, the use of CALCULATE or not CALCULATE really represents two wildly different programming approaches and philosophies around DAX coding.
Variables
This may seem unrelated, but my path towards my current disuse of CALCULATE really all started with my use of variables. And I will forever be indebted to @konstantinos for introducing me to the concept of variables in DAX. Truly revolutionized my thinking about what was possible with DAX. In my opinion, the use or non-use of DAX variables is a key, fundamental difference in programming approaches and philosophies.
Prior to variables, DAX coding was pretty unintuitive and; quite frankly, ugly. You would end up with these long, ugly, nested to the n'th degree DAX calculations that were difficult to create and even worse to troubleshoot. Or, you would end up with 63,000 measures that was a absolute debugging trainwreck. Or both.
In the first case, infinitely nested DAX is awful to deal with. You essentially have to create the code from the inside out and it's just an unnatural experience overall. To debug it, you also have to read it inside out. Also unnatural. To troubleshoot it once it is created is sheer frustration trying to comment out bits and pieces of code above and below the part you are interested in. Not impossible but super annoying.
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.
Variables tend to solve these issues. 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. Finally, variables prevent duplication of code such as in this classic:
Measure =
VAR __Sum = SUM('Table'[Column])
RETURN
IF(ISBLANK(__Sum),0,__Sum)
Now think if the equivalent of __Sum is some long, nested DAX statement and you have to repeat it twice in your DAX forumla...
OK, so that's why I really like to use variables and I use them a lot. In fact, I will tend to use them in even simple calculations that could be done in a single DAX statement just because its generally likely that DAX calculations become more complex over time so might as well start with a good base model that can be extended later.
What Do Variables Have to Do with CALCULATE?
So I know what you are thinking. What in the world does the use of variables have to do with using or not using CALCULATE? Well, the answer is quite simple really. Once you start to use variables you tend to graduate to using table variables. And once you are using tables variables, CALCULATE pretty much becomes useless. Let me demonstrate with a quick example. The following measure does not work:
Measure =
VAR __Table = { ("One",1), ("Two",2), ("Three",3) }
RETURN
CALCULATE(SUM([Value2]),__Table)
CALCULATE can't comprehend the Value2 column. Sure, even though calculate's second argument accepts a "table filter expression", this is NOT the same as accepting any expression that returns a table. For calculate to work, everything still needs to be grounded in actual tables within the data model. Conversely, this works just fine:
Measure =
VAR __Table = { ("One",1), ("Two",2), ("Three",3) }
RETURN
SUMX(__Table,[Value2])
Unlike CALCULATE, the "X" aggregation functions can operate on virtual tables with ease as well as on actual tables in the data model.
It is because of this fact that I really started to eschew CALCULATE for other methods. Call me lazy or maybe I was dropped on my head as a baby or perhaps MAMA DIDN'T LOVE ME. Who can say, but if I have two ways of doing something and one works all the time and the other only works some of the time, I tend to go with the one that works all the time unless there is an incredibly pressing reason not to. For coding, I see it as a code maintainability thing. If there are multiple ways of solving a recurring coding construct, I want everyone on the team using the same method if at all possible. Having six or seven different ways of doing the same thing is just heaping technical debt upon technical debt for no good reason. In point of fact, I cannot think of a single thing that can be done with CALCULATE that can't be done via some other method, which kind of makes it superfluous.
Let's face it, Measure Totals the Final Word, you can't solve that problem using CALCULATE. In fact, it's right around that 2017/2018 time frame that I started to understand that CALCULATE was more of a shackle than something helpful.
Reality
Alright, so hopefully by now you are starting to get the picture about why I tend not to use CALCULATE but I'm not quite done. You see, it is the fact that CALCULATE is so tied to the actual tables in the data model that is perhaps its greatest downfall. In fact, most non-trivial DAX calculations are not possible by using CALCULATE unless you happen to have a 100% idealized data model. You can see this in Microsoft's own DAX examples as well as the examples on daxpatterns.com, etc. In almost every case where CALCULATE is used, it is dealing with the AdventureWorks database, the idealized of all idealized data models. Here CALCULATE can shine because the calculations are really pretty trivial thanks to a, let's all be honest, too perfect data model.
Reality check, the vast majority of data models that are used in Power BI are far, far from being optimized or in any way ideal. The reality is that 80-90% of the people using Power BI are not even professional IT people, let alone professional data modelers. It's just not realistic. And, as you get further and further away from an idealized data model, the less and less useful CALCULATE tends to be.
Sure, in theory, CALCULATE is a super useful function. However, once you inject a bit of reality, it is far, far less useful. I can't help it, I was educated as an engineer. Theory is great and all, but if you can't apply it who really cares?
Performance
OK, this article would not be complete if I did not address perhaps one of the biggest reasons why people tout CALCULATE, performance. I won't argue with any of the information about how and why CALCULATE is super performant. 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.
Conclusion
At the end of the day, I'm not here to convince you one way or another about using or not using CALCULATE. I just felt like explaining why I do not tend to use CALCULATE.
In short, I don't use CALCULATE because I've developed a method of solving complex DAX calculations that works every time. Create a table variable to represent your data. Use an "X" aggregator function to perform a calculation across that table. This method can solve any problem that CALCULATE can plus solve calculations that are impossible for CALCULATE. For me it's about the pattern and process to achieve success versus constantly kowtowing to the feeble limitations of CALCULATE. Most of my 150+ Quick Measures in the Gallery I would have never solved had I remained subservient to CALCULATE's way of thinking and that alone in my mind is reason enough not to use it.
I actually think this is a fascinating subject that strikes to the heart of a lot of DAX coding practices and philosophies. Maybe I'm wrong about everything. Either way, I would really like to get a discussion going on this subject.
- « Previous
-
- 1
- 2
- 3
- Next »
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Seamless Power BI Report Management with SharePoin...
- Field Parameters in Power BI
- #PBI10 Dataviz Contest | Frequently Asked Question...
- How to Organize Measures Effectively in Power BI
- Power BI Enhancements You Need to Know - Part 6: P...
- #PBI10 Dataviz Contest | Meet the Judges
- Dataviz Contests - How to Submit
- #PBI10 Dataviz Contest
- Unlock Deeper Insights: Data Modeling Best Practic...
- How to Display Power BI Reports in a Continuous Sl...
-
ajaybabuinturi
on: Seamless Power BI Report Management with SharePoin...
-
Ilgar_Zarbali on: Field Parameters in Power BI
- AbdulBasha on: How to Organize Measures Effectively in Power BI
-
Magudeswaran_MR
on: Power BI Enhancements You Need to Know - Part 6: P...
-
technolog on: #PBI10 Dataviz Contest | Meet the Judges
- HerraZhang on: Dataviz Contests - How to Submit
-
edchuy
on: #PBI10 Dataviz Contest
- TharunChalla on: What Power BI Developers Often Miss and Why Does I...
-
Magudeswaran_MR
on: Power BI Enhancements You Need to Know – Part 5: O...
-
Magudeswaran_MR
on: The Power of Automation: One-Click DAX Formulas an...
-
How to
716 -
Tips & Tricks
694 -
Events
143 -
Support insights
121 -
Opinion
98 -
DAX
66 -
Power BI
65 -
Power Query
62 -
Power BI Dev Camp
45 -
Power BI Desktop
40 -
Roundup
39 -
Dataflow
23 -
Featured User Group Leader
21 -
Power BI Embedded
20 -
Time Intelligence
19 -
Tips&Tricks
18 -
PowerBI REST API
12 -
Data Protection
11 -
Power Query Tips & Tricks
8 -
finance
8 -
Power BI Service
8 -
Direct Query
7 -
Power BI REST API
6 -
Auto ML
6 -
financial reporting
6 -
Data Analysis
6 -
Power Automate
6 -
Data Visualization
6 -
Python
6 -
Income Statement
5 -
Dax studio
5 -
powerbi
5 -
service
5 -
Power BI PowerShell
5 -
Machine Learning
5 -
community
4 -
RLS
4 -
M language
4 -
Paginated Reports
4 -
External tool
4 -
Power BI Goals
4 -
PowerShell
4 -
Desktop
4 -
Bookmarks
4 -
Line chart
4 -
Group By
4 -
Data model
3 -
Conditional Formatting
3 -
Visualisation
3 -
Life Sciences
3 -
Administration
3 -
M code
3 -
Visuals
3 -
SQL Server 2017 Express Edition
3 -
R script
3 -
Aggregation
3 -
calendar
3 -
Gateways
3 -
R
3 -
M Query
3 -
Webinar
3 -
CALCULATE
3 -
R visual
3 -
Reports
3 -
PowerApps
3 -
Data Science
3 -
Azure
3 -
Number Ranges
2 -
Query Plans
2 -
Power BI & Power Apps
2 -
Random numbers
2 -
Day of the Week
2 -
Custom Visual
2 -
VLOOKUP
2 -
pivot
2 -
calculated column
2 -
M
2 -
hierarchies
2 -
Power BI Anniversary
2 -
Language M
2 -
inexact
2 -
Date Comparison
2 -
Power BI Premium Per user
2 -
Forecasting
2 -
REST API
2 -
Editor
2 -
Split
2 -
measure
2 -
Microsoft-flow
2 -
Paginated Report Builder
2 -
Working with Non Standatd Periods
2 -
powerbi.tips
2 -
Custom function
2 -
Reverse
2 -
PUG
2 -
Custom Measures
2 -
Filtering
2 -
Row and column conversion
2 -
Python script
2 -
Nulls
2 -
DVW Analytics
2 -
parameter
2 -
Industrial App Store
2 -
Week
2 -
Date duration
2 -
Formatting
2 -
Weekday Calendar
2 -
Support insights.
2 -
construct list
2 -
slicers
2 -
SAP
2 -
Power Platform
2 -
Workday
2 -
external tools
2 -
index
2 -
RANKX
2 -
PBI Desktop
2 -
Date Dimension
2 -
Integer
2 -
Visualization
2 -
Power BI Challenge
2 -
Query Parameter
2 -
Date
2 -
SharePoint
2 -
Power BI Installation and Updates
2 -
How Things Work
2 -
Tabular Editor
2 -
rank
2 -
ladataweb
2 -
Troubleshooting
2 -
Date DIFF
2 -
Transform data
2 -
Healthcare
2 -
Tips and Tricks
2 -
Incremental Refresh
2 -
Governance
1 -
Fun
1 -
Power BI gateway
1 -
gateway
1 -
Elementary
1 -
Custom filters
1 -
Vertipaq Analyzer
1 -
powerbi cordoba
1 -
Model Driven Apps
1 -
REMOVEFILTERS
1 -
XMLA endpoint
1 -
translations
1 -
OSI pi
1 -
Parquet
1 -
Change rows to columns
1 -
remove spaces
1 -
Get row and column totals
1 -
Retail
1 -
Power BI Report Server
1 -
School
1 -
Cost-Benefit Analysis
1 -
DIisconnected Tables
1 -
Sandbox
1 -
Honeywell
1 -
Combine queries
1 -
X axis at different granularity
1 -
ADLS
1 -
Primary Key
1 -
Microsoft 365 usage analytics data
1 -
Randomly filter
1 -
Week of the Day
1 -
Azure AAD
1 -
query
1 -
Dynamic Visuals
1 -
KPI
1 -
Intro
1 -
Icons
1 -
ISV
1 -
Ties
1 -
unpivot
1 -
Practice Model
1 -
Continuous streak
1 -
ProcessVue
1 -
Create function
1 -
Table.Schema
1 -
Acknowledging
1 -
Postman
1 -
Text.ContainsAny
1 -
Power BI Show
1 -
Get latest sign-in data for each user
1 -
API
1 -
Kingsley
1 -
Merge
1 -
variable
1 -
Issues
1 -
function
1 -
stacked column chart
1 -
ho
1 -
ABB
1 -
KNN algorithm
1 -
List.Zip
1 -
optimization
1 -
Artificial Intelligence
1 -
Map Visual
1 -
Text.ContainsAll
1 -
Tuesday
1 -
help
1 -
group
1 -
Scorecard
1 -
Json
1 -
Tops
1 -
financial reporting hierarchies RLS
1 -
Featured Data Stories
1 -
MQTT
1 -
Custom Periods
1 -
Partial group
1 -
Reduce Size
1 -
FBL3N
1 -
Wednesday
1 -
Power Pivot
1 -
Quick Tips
1 -
data
1 -
PBIRS
1 -
Usage Metrics in Power BI
1 -
Multivalued column
1 -
Pipeline
1 -
Path
1 -
Yokogawa
1 -
Dynamic calculation
1 -
Data Wrangling
1 -
native folded query
1 -
transform table
1 -
UX
1 -
Cell content
1 -
General Ledger
1 -
Thursday
1 -
Table
1 -
Natural Query Language
1 -
Infographic
1 -
automation
1 -
Prediction
1 -
newworkspacepowerbi
1 -
Performance KPIs
1 -
HR Analytics
1 -
keepfilters
1 -
Connect Data
1 -
Financial Year
1 -
Schneider
1 -
dynamically delete records
1 -
Copy Measures
1 -
Friday
1 -
Q&A
1 -
Event
1 -
Custom Visuals
1 -
Free vs Pro
1 -
Format
1 -
Active Employee
1 -
Custom Date Range on Date Slicer
1 -
refresh error
1 -
PAS
1 -
certain duration
1 -
DA-100
1 -
bulk renaming of columns
1 -
Single Date Picker
1 -
Monday
1 -
PCS
1 -
Saturday
1 -
update
1 -
Slicer
1 -
Visual
1 -
forecast
1 -
Regression
1 -
CICD
1 -
Current Employees
1 -
date hierarchy
1 -
relationship
1 -
SIEMENS
1 -
Multiple Currency
1 -
Power BI Premium
1 -
On-premises data gateway
1 -
Binary
1 -
Power BI Connector for SAP
1 -
Sunday
1 -
Training
1 -
Announcement
1 -
Features
1 -
domain
1 -
pbiviz
1 -
sport statistics
1 -
Intelligent Plant
1 -
Circular dependency
1 -
GE
1 -
Exchange rate
1 -
Dendrogram
1 -
range of values
1 -
activity log
1 -
Decimal
1 -
Charticulator Challenge
1 -
Field parameters
1 -
deployment
1 -
ssrs traffic light indicators
1 -
SQL
1 -
trick
1 -
Scripts
1 -
Color Map
1 -
Industrial
1 -
Weekday
1 -
Working Date
1 -
Space Issue
1 -
Emerson
1 -
Date Table
1 -
Cluster Analysis
1 -
Stacked Area Chart
1 -
union tables
1 -
Number
1 -
Start of Week
1 -
Tips& Tricks
1 -
Workspace
1 -
Theme Colours
1 -
Text
1 -
Flow
1 -
Publish to Web
1 -
Extract
1 -
Topper Color On Map
1 -
Historians
1 -
context transition
1 -
Custom textbox
1 -
OPC
1 -
Zabbix
1 -
Label: DAX
1 -
Business Analysis
1 -
Supporting Insight
1 -
rank value
1 -
Synapse
1 -
End of Week
1 -
Tips&Trick
1 -
Showcase
1 -
custom connector
1 -
Waterfall Chart
1 -
Power BI On-Premise Data Gateway
1 -
patch
1 -
Top Category Color
1 -
A&E data
1 -
Previous Order
1 -
Substring
1 -
Wonderware
1 -
Power M
1 -
Format DAX
1 -
Custom functions
1 -
accumulative
1 -
DAX&Power Query
1 -
Premium Per User
1 -
GENERATESERIES
1 -
Report Server
1 -
Audit Logs
1 -
analytics pane
1 -
step by step
1 -
Top Brand Color on Map
1 -
Tutorial
1 -
Previous Date
1 -
XMLA End point
1 -
color reference
1 -
Date Time
1 -
Marker
1 -
Lineage
1 -
CSV file
1 -
conditional accumulative
1 -
Matrix Subtotal
1 -
Check
1 -
null value
1 -
Excel
1 -
Cumulative Totals
1 -
Report Theme
1 -
Bookmarking
1 -
oracle
1 -
mahak
1 -
pandas
1 -
Networkdays
1 -
Button
1 -
Dataset list
1 -
Keyboard Shortcuts
1 -
Fill Function
1 -
LOOKUPVALUE()
1 -
Tips &Tricks
1 -
Plotly package
1 -
Sameperiodlastyear
1 -
Office Theme
1 -
matrix
1 -
bar chart
1 -
Measures
1 -
powerbi argentina
1 -
Canvas Apps
1 -
total
1 -
Filter context
1 -
Difference between two dates
1 -
get data
1 -
OSI
1 -
Query format convert
1 -
ETL
1 -
Json files
1 -
Merge Rows
1 -
CONCATENATEX()
1 -
take over Datasets;
1 -
Networkdays.Intl
1 -
refresh M language Python script Support Insights
1
- 06-29-2025 - 07-05-2025
- 06-22-2025 - 06-28-2025
- 06-15-2025 - 06-21-2025
- 06-08-2025 - 06-14-2025
- 06-01-2025 - 06-07-2025
- 05-25-2025 - 05-31-2025
- 05-18-2025 - 05-24-2025
- 05-11-2025 - 05-17-2025
- 05-04-2025 - 05-10-2025
- 04-27-2025 - 05-03-2025
- 04-20-2025 - 04-26-2025
- 04-13-2025 - 04-19-2025
- 04-06-2025 - 04-12-2025
- View Complete Archives