DAX: Visual Calculations and Measures
- Jihwan Kim
- Jul 20
- 5 min read
In this writing, I’d like to share how I learn to navigate the evolving world of DAX. In an era dominated by AI and low-code solutions, it's easy to get distracted by the constant stream of new features. I see the "Copilot madness" in community forums, and I understand it. While these tools are powerful assistants, they haven't replaced the need for deep, foundational expertise. True mastery of Data Analysis Expressions (DAX) remains the ultimate differentiator for building robust, insightful, and reliable analytics solutions.
The Two DAX Headaches
Let's consider two common scenarios that most of Power BI developer faces.
The Visual-Specific Calculation: An analyst needs to show a "percent of parent total" in a matrix. It's a simple request, but writing the DAX measure can be surprisingly complex, involving intricate CALCULATE statements and context manipulation. The measure works for that one visual, but it's clunky and not easily reusable elsewhere.
The Brittle Measure: A developer writes a clever measure using VALUES to get a distinct list of products. It works perfectly. Six months later, a change in the data source introduces a blank row in the relationship, and suddenly the measure breaks, causing downstream reports to fail. The logic was correct for the data at the time, but it wasn't resilient.
These scenarios highlight the two frontiers of DAX development today: simplifying visual-level calculations and building enterprise-grade measures that can withstand the test of time.
New Tools and Timeless Wisdom
Let's tackle these challenges with a combination of new features and battle-tested best practices.
Part 1: Quick Wins with Visual Calculations
Microsoft has introduced a way to write DAX called "visual calculations." These are expressions that operate directly on the aggregated data I see in a visual, which dramatically simplifies many common calculations. Think of them as a lightweight DAX layer that sits on top of the visual. Instead of writing complex measures in my model, I can add calculations like running sums or moving averages with incredibly simple functions. The new family of functions includes RUNNINGSUM, MOVINGAVERAGE, and axis-navigation functions like FIRST, LAST, NEXT, and PREVIOUS.
Sample data model
For example, a running total or a running sum that once required a complex CALCULATE pattern can now be written directly on the visual as:
Running sum = RUNNINGSUM([Sales:], ORDERBY([Sales:],DESC))Note:
This visual calculation creates a running total of sales, but in a specific order. Instead of accumulating based on the visual's default sort order (like by ModelName), it calculates the sum starting from the item with the highest sales and moving down to the item with the lowest sales.
The ORDERBY function controls the sequence in which the RUNNINGSUM function performs its calculation. It doesn't change the visual sorting of the table or chart, but it dictates the "path" the calculation takes through the data.
[Sales:]: This specifies that the calculation order should be based on the [Sales:] value itself.
DESC: This sets the direction to descending.
So, ORDERBY([Sales:], DESC) tells RUNNINGSUM to start with the data point having the largest [Sales:] value, then add the second-largest, then the third-largest, and so on.
This specific pattern is frequently used to create a Pareto analysis. By calculating the running sum based on the contribution (sales in this case), I can easily see which products contribute to 80% of the total sales, helping me identify my most important items.
A Strategic Guide: When to Use Visual Calculations
Visual calculations are a fantastic tool, but they have a specific purpose. They are a "gateway" to DAX, not a replacement for it. Because they exist only within the context of a single visual, they cannot be reused in other visuals or referenced by my core semantic model measures.
Use Visual Calculations for: Quick, ad-hoc analysis and visual-specific formatting. They are perfect for report authors who need to add a simple comparison or calculation without modifying the central data model.
Use Model Measures for: Core business logic. Any calculation that needs to be consistent, reusable, and centrally governed, like [Profit Margin], belongs in the semantic model.
Part 2: The DAX Measures: Writing Bulletproof Measures
While visual calculations offer simplicity, the heart of enterprise BI lies in writing resilient, maintainable measures. This is a form of defensive programming. We're not just writing DAX for the data we have today, but for the model we'll have tomorrow. Here are a few advanced patterns, inspired by the deep analysis from the experts at SQLBI, that I've incorporated into my practice.
Tip 1: Taming the Dangerous ALLSELECTED
The ALLSELECTED function is essential for creating "percent of visual total" calculations, but its power comes with risk. It works by removing the filter context from the fields in the current visual, while preserving the filter context coming from outside (like slicers or other visuals). The danger lies in misunderstanding this behavior. A common pitfall is using it in a complex report and getting unexpected results because it respects an external filter you forgot about. The best practice is to use ALLSELECTED deliberately, always being fully aware of all the external filters that might affect my visual, to ensure my percentages are calculated against the precise slice of data I intend.
https://www.sqlbi.com/articles/allselected-best-practices/
▶️ The following measures are intended to highlight the differences, not to be the most optimized versions.
Sales ALLSELECTED: =
CALCULATE ( [Sales:], ALLSELECTED ( ) )Sales %: =
SUMX (
SUMMARIZE ( dimproduct, dimproduct[ModelName], dimproduct[Color] ),
DIVIDE ( [Sales:], [Sales ALLSELECTED:] )
)Sales % correct: =
VAR _SalesAllselected =
CALCULATE ( [Sales:], ALLSELECTED () )
RETURN
SUMX (
SUMMARIZE ( dimproduct, dimproduct[ModelName], dimproduct[Color] ),
DIVIDE ( [Sales:], _SalesAllselected )
)
Tip 2: The DISTINCT vs. VALUES Debate: A Contextual Choice
When I need a unique list of values from a column, both VALUES and DISTINCT are powerful tools, but their behavior differs in a subtle yet critical way that can impact my calculations. The choice depends entirely on my business requirements. VALUES can return a blank row if data integrity issues exist in a relationship. This can be the intended behavior if, for example, I need to explicitly account for sales that are not tied to a valid product. DISTINCT, in contrast, only returns values that physically exist in the column, effectively ignoring orphaned records. Therefore, the decision is a strategic one: use VALUES when I need to be aware of and potentially handle data with broken relationships, and use DISTINCT when I logic requires a clean list of only the valid values present in the column itself. Understanding this distinction is key to writing DAX that behaves exactly as my business context demands.
https://www.sqlbi.com/articles/choosing-between-distinct-and-values-in-dax/
▶️ The following measures are intended to highlight the differences, not to be the most optimized versions.
Sales % VALUES: =
VAR _salesall =
CALCULATE (
SUMX ( VALUES ( dimsalesterritory[SalesTerritoryCountry] ), [Sales:] ),
REMOVEFILTERS ( dimsalesterritory[SalesTerritoryCountry] )
)
RETURN
DIVIDE ( [Sales:], _salesall )Sales % DISTINCT: =
VAR _salesall =
CALCULATE (
SUMX ( DISTINCT ( dimsalesterritory[SalesTerritoryCountry] ), [Sales:] ),
REMOVEFILTERS ( dimsalesterritory[SalesTerritoryCountry] )
)
RETURN
DIVIDE ( [Sales:], _salesall )
In the Age of AI, Craftsmanship Matters More Than Ever
The evolution of DAX is a perfect reflection of the broader trends in the industry. We have new tools like visual calculations that lower the barrier to entry and empower more people to perform simple analyses. At the same time, the need for deep, expert-level knowledge has never been greater.
An AI assistant can generate a basic DAX formula. It might even get the syntax right for a visual calculation. But it cannot yet replicate the nuanced reasoning required to architect a resilient, optimized, and maintainable semantic model. It doesn't yet understand the subtle difference between VALUES and DISTINCT in the context of my specific data model's potential future state.
This is where our value as data professionals lies. Our job is to be the architects, the craftsmen who build solutions that are not only accurate today but robust enough to handle the complexities of tomorrow. Mastering these DAX patterns is how we demonstrate that craftsmanship.
Conclusion
I hope this helps having fun in exploring both the new, simplified world of visual calculations and the timeless, powerful patterns of resilient DAX.
The best developers I know are masters of both.
コメント