DAX Synatx
DAX Syntax explained.
DAX (Data Analysis Expressions) is a formula language used to define custom calculations and logic in tools like Power BI, Excel Power Pivot, and SSAS Tabular.
Basic Structure of a DAX Expression
A DAX formula usually follows this structure:
NewName = DAX_Function(Arguments)
NewName: The name of the column, measure, or table you're creating.=: Assignment operator.DAX_Function: The DAX function or expression to perform the calculation.Arguments: Input values, columns, tables, or nested functions.
Example (Measure)
Total Sales = SUM(Sales[Amount])
Total Sales: Name of the measure.=: Assignment.SUM: DAX function.Sales[Amount]: Refers to theAmountcolumn in theSalestable.
Key DAX Syntax Rules
| Rule | Description |
|---|---|
| Comments | Use // for single-line comments |
| Case Insensitive | DAX is not case-sensitive (SUM, sum, Sum are all valid) |
| Tables and Columns | Use TableName[ColumnName] to refer to a specific column |
| Functions | Similar to Excel, but tailored for data models |
| Text Strings | Enclosed in double quotes ("Text") |
| Variables | Use VAR to declare variables and RETURN to output |
| Error Handling | Use functions like IFERROR, TRY, or IF for logic |
Common Syntax Elements
| Element | Example | Purpose |
|---|---|---|
| Column Reference | Sales[Revenue] | Access a column value |
| Table Reference | Sales | Access an entire table |
| Function Call | SUM(Sales[Revenue]) | Aggregate values |
| Variable | VAR x = 100 | Store temporary values |
| Logical Test | IF(Sales[Revenue] > 1000, "High", "Low") | Conditional logic |
Example with Variables
Profit Margin =
VAR Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost])
VAR Margin = Profit / SUM(Sales[Revenue])
RETURN Margin
VARstores intermediate steps.RETURNoutputs the final result.- This is easier to read, debug, and reuse than long inline formulas.
No questions available.