What Is DAX?
DAX (Data Analysis Expressions) is a functional formula language designed specifically for working with relational and tabular data in Power BI, Excel Power Pivot, and SQL Server Analysis Services Tabular models. Unlike Excel formulas that operate on individual cells, every DAX formula operates on entire columns or tables and always returns either a single scalar value or a table. DAX formulas are built by combining functions such as SUM, CALCULATE, and FILTER with operators and references to columns using the Table[Column] syntax.
Cricket analogy: Just as a batsman's strike rate is calculated from an entire innings' worth of balls faced rather than a single delivery, a DAX formula like SUM(Sales[Amount]) always evaluates across a whole column of rows, not one cell at a time.
DAX Syntax Fundamentals
DAX syntax uses fully qualified references such as Sales[Amount] to point to a column inside a specific table, along with standard operators: + - * / for arithmetic, & for text concatenation, and =, >, <, <> for comparisons. Functions can be nested inside one another, for example SUM(FILTER(Sales, Sales[Region]="West")[Amount]), and single-line comments start with -- or //, while block comments use /* */. Function names and table/column identifiers are not case-sensitive, but Power BI's auto-formatting capitalizes function names for readability.
Cricket analogy: A scorecard entry like 'Kohli, India' fully qualifies a player to his team the way Sales[Amount] fully qualifies a column to its table, avoiding ambiguity when multiple teams share a batsman's surname.
Data Types and Blanks
DAX supports whole number, decimal number, fixed decimal (currency), date/time, text, and TRUE/FALSE boolean types, and it performs implicit conversions automatically, so the text string "5" added to the number 3 evaluates to 8. A missing or empty value is represented as BLANK(), and BLANK() behaves specially in arithmetic: BLANK() + 5 returns 5, but BLANK() multiplied by 5 returns BLANK(), which frequently surprises new DAX authors building ratio measures.
Cricket analogy: A 'Did Not Bat' entry on a scorecard isn't scored as a zero but treated as genuinely absent, similar to how BLANK() in DAX is distinct from 0 and propagates differently through arithmetic.
// Measure: Total Sales
Total Sales = SUM(Sales[Amount])
// Measure: Profit Margin %
Profit Margin % =
DIVIDE(
SUM(Sales[Amount]) - SUM(Sales[Cost]),
SUM(Sales[Amount])
)Calculated Columns vs Measures
Calculated columns are evaluated row by row during data refresh and are physically stored in the model, consuming memory just like imported columns, which makes them appropriate for row-level classifications like a Margin or Price Tier column used in slicers. Measures, by contrast, are evaluated on the fly at query time using the current filter context from slicers, rows, and columns in a visual, and they are never stored, which is why a measure like Total Sales := SUM(Sales[Amount]) always reflects whatever filters are currently applied.
Cricket analogy: A player's fixed jersey number is set once and stored on the team sheet like a calculated column, while a live 'runs today' scoreboard total recalculates constantly as the match filter context changes, just like a measure.
DAX function names are not case-sensitive — sum(), Sum(), and SUM() all work identically — but Power BI Desktop auto-formats them to uppercase (SUM, CALCULATE, FILTER) when you commit a formula, and following this convention makes formulas far easier to read and debug.
- DAX formulas always return a single scalar value or a table.
- Table[Column] syntax fully qualifies a column reference to its table.
- Calculated columns are computed row-by-row at refresh time and stored in the model.
- Measures are computed at query time based on filter context and are never stored.
- BLANK() represents a true absence of a value and behaves differently from 0 in arithmetic.
- DAX is used across Power BI Desktop, Excel Power Pivot, and SSAS Tabular models.
- Implicit type conversion lets DAX combine text and numbers automatically, e.g. "5" + 3 = 8.
Practice what you learned
1. What does every valid DAX formula return?
2. Which statement correctly describes calculated columns?
3. What does BLANK() + 5 evaluate to in DAX?
4. Which syntax correctly references the Amount column in the Sales table?
5. Which of these is NOT a common environment where DAX is used?
Was this page helpful?
You May Also Like
DAX Aggregation Functions
Aggregation functions like SUM, AVERAGE, COUNT, and their X-suffixed iterator counterparts are the workhorses for summarizing data in DAX measures.
Filter Context and CALCULATE
Filter context determines which rows a DAX measure sees, and CALCULATE is the only function that can directly reshape that context.
DAX Variables and Iterators
VAR and RETURN make complex DAX measures more readable and performant by caching intermediate results, especially when combined with iterator functions.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics