- 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
Recently Ed Hansberry posted a fantastic article, Why You Should Avoid Calculated Columns in Power BI — ehansalytics. It's a great article in which Ed clearly articulates the standard Power BI best practice/mantra which can be summarized as:
- If you have to use calculated columns, create those in Power Query or the source instead of DAX
- Even better, use DAX measures instead of calculated columns
Now, I won't reiterate the reasons here, it has to do with model size, refresh performance, etc. Read the article, I highly recommend it. However, as is too often the case with most best practices, a lot of nuance is generally glossed over. This is certainly true for the mantra in question, the use of DAX calculated columns. So, if you have read the best practices around this and are concerned about your use of DAX calculated columns, let me assure you that it is not the end of the world. Here's why.
Scope
The best practices around DAX calculated columns are really geared toward data modeling professionals, not business users. And let's face it, professional data modelers probably only represent a small fraction, maybe 10%-20% of the Power BI user base. Professional data modelers are highly technical and are likely versed in a variety of programming languages such as SQL, Power Query (M), as well as DAX, R, Python, you name it.
Let's contrast this with a typical business user of Power BI. Most business users likely gravitate initially towards DAX because it has a familiar feel and many similar functions to Excel. Being somewhat less technical than professional data modelers, this may end up being the only language they ever really use.
It is also quite likely that these business users do not have access to source systems and probably wouldn't even know where to begin even if they had access. And, Power Query (M) is an entirely different sort of programming language than something like DAX or Excel functions.
None of this is to insult the average business user but they are a business user and not a professional data modeler for a reason. They know more about the business and less about the technical stuff.
The point is, that requiring business users to learn something other than DAX really amps up the learning curve for Power BI and that's not really what anyone wants. We want more people to adopt and use Power BI, not less.
Scale
Worrying about how much a calculated column is compressed, its cardinality and how much storage it takes up in the model really only comes into play at scale, like in the 200 million row table from Ed's example. And even then, the calculated column still only takes up less than 1% of the total storage for the model.
So, again, for professional data modelers dealing with enterprise scale data, these sorts of things might matter. But for the typical business user dealing with tables with rows that number in the hundreds, thousands, tens of thousands or even a few million, the additional storage consumed and perhaps performance are barely noticeable, if at all. At the end of the day, it simply does not matter.
Maintainability
Another aspect of this discussion is overall maintainability of the solution, a subject that is too often overlooked. In most software solutions, writing a system in three different languages is generally considered a bad idea. So if you implement a software system partly in PHP, some C# and some Java, that's a lot of extra technical debt to incur versus writing the system in a single language. It takes additional expertise to work on the system and make changes, fix bugs, etc. because you have to know all three different languages. So, there's something to be said for just using a single language when implementing software.
Now, consider this with respect to the guidance that DAX calculated columns are bad. Well, if you are already going to have DAX measures and you can also write your calculated columns in DAX, that's pretty good maintainability. Single language, single place to make changes, etc. Contrast that with the case where some of your custom columns are in SQL, some are in Power Query and some are in DAX. That's pretty poor overall maintainability.
Data Refresh Performance
It is generally argued that creating columns in Power Query is better than DAX because data refreshes perform better. Well, not always. If your calculated column in Power Query breaks query folding, then it's quite likely that you just decreased performance rather than increased performance over a DAX calculated column. So, absolutes are dangerous.
Measure vs. Columns
This is another "best practice" that needs some nuance. Look, measures are great, but they have some problems. First, they require a bit more technical expertise than calculated columns because you are dealing with filter context (in other words "filtering"). Second, they can often suffer from the dreaded "measure totals problem" whereas calculated columns never suffer such issues. So, again, measures tend to increase the technical complexity involved in a solution and this can be burdensome to business users just trying to do some basic analysis of their data.
Conclusion
Again, I want to be clear, Ed's article is a great article and there is nothing "wrong" with the advice and guidance provided. It is absolutely the generally accepted "best practice" when it comes to professional data modeling. The point here is that as with all such "best practices", the application of them is seldom, if ever, universal. So, if you are worried that your DAX calculated columns are going to bring about the apocalypse, don't. If you are part of the 80%-90% of Power BI's target market, business users, you almost certainly don't have to worry about the issue in the slightest.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Unlocking the Power of Calculation Group - Beyond ...
- 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...
- Someshn on: Unlocking the Power of Calculation Group - Beyond ...
-
Abhilash_P
on: Seamless Power BI Report Management with SharePoin...
- RONF 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
-
FabricPam 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...
-
How to
717 -
Tips & Tricks
695 -
Events
143 -
Support insights
121 -
Opinion
99 -
DAX
66 -
Power BI
65 -
Power Query
62 -
Power BI Dev Camp
45 -
Power BI Desktop
40 -
Roundup
39 -
Dataflow
22 -
Featured User Group Leader
21 -
Power BI Embedded
20 -
Time Intelligence
19 -
Tips&Tricks
18 -
PowerBI REST API
12 -
Data Protection
11 -
Power BI Service
8 -
Power Query Tips & Tricks
8 -
finance
8 -
Direct Query
7 -
Auto ML
6 -
financial reporting
6 -
Data Analysis
6 -
Power Automate
6 -
Data Visualization
6 -
Python
6 -
Power BI REST API
6 -
powerbi
5 -
service
5 -
Power BI PowerShell
5 -
Machine Learning
5 -
Income Statement
5 -
Dax studio
5 -
Power BI Goals
4 -
PowerShell
4 -
Desktop
4 -
Bookmarks
4 -
Line chart
4 -
Group By
4 -
community
4 -
RLS
4 -
M language
4 -
Paginated Reports
4 -
External tool
4 -
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 -
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 -
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 -
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 -
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 -
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
- 07-06-2025 - 07-10-2025
- 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