Few languages divide people who work in Power BI as much as DAX. At first it seems impenetrable: formulas that look like Excel but behave differently, results that change with context for no apparent reason. And yet a surprisingly small set of patterns solves the vast majority of reports. The secret is not memorizing hundreds of functions, it is understanding how DAX thinks.
This article is not a list of formulas to copy. It is a map of the measures and concepts that, once mastered, cover about 80 percent of real analysis needs.
First, the concept that changes everything: context
Before any measure, you need to internalize one idea: in DAX, the result of a formula depends on the context in which it is evaluated. The same "total sales" calculation gives one number on a product row and another on the grand total, because the filter context is different. There are two contexts: filter context (what the visual, filters and slicers apply) and row context (the row-by-row evaluation in a table).
Those who understand filter context stop being surprised and start controlling DAX. Those who do not, fight it forever.
CALCULATE: the function that unlocks everything
If there were a single function to master, it would be CALCULATE. It is what lets you change the filter context of a calculation: "sales, but only for last year", "margin, ignoring the region filter". Practically every advanced pattern is a variation of CALCULATE. Investing time to understand it deeply is the best return a Power BI analyst can get.
The patterns that pay off day to day
With context and CALCULATE internalized, a handful of patterns solve most requests:
- Time intelligence: period-over-period comparisons (versus previous year, year to date, previous month). These are among the most frequent requests and, with a good date table, among the simplest to solve.
- Running totals and moving averages: to see trends instead of isolated values.
- Safe ratios: divisions that handle zero and blank with the DIVIDE function, avoiding errors in reports.
- Dynamic rankings: with RANKX, to order products, customers or stores reactively to filters.
- Percentage of total: how much each category weighs in the whole, a classic that combines CALCULATE with removing filters.
In practice: fewer measures, more reuse
A common mistake is creating dozens of near-identical measures, such as sales, last-year sales, sales in euros, sales by channel, until the model becomes impossible to maintain. The mature approach is the opposite: a few well-built base measures (one "sales" measure) and then reusable patterns that reuse them, changing only the context.
Imagine an analyst who needed half a dozen measures for each time-based indicator. By adopting a base sales measure and applying time intelligence patterns with CALCULATE, they went from dozens of fragile measures to a handful of solid patterns that serve any indicator. The report became faster to build and much easier to maintain.
Where to start
If you are starting out, resist the temptation to collect formulas. Begin with three things: understanding filter context, mastering CALCULATE, and building a good date table. With this foundation, the patterns above stop being mysteries and become tools. DAX stops being an adversary and becomes what it should have been all along: an expressive way to turn data into answers.
And your team: is it fighting DAX with dozens of ad-hoc measures, or building on patterns that get reused?
