How to Write DAX Formula in Power BI: Simple Guide
To write a
DAX formula in Power BI, start by selecting a table or creating a new measure, then use = followed by your expression using functions and columns. DAX formulas combine functions, operators, and references to calculate values dynamically in your reports.Syntax
A DAX formula starts with an equal sign = followed by a combination of functions, column names, and operators. You can write formulas for calculated columns or measures.
- =: Indicates the start of a formula.
- Function(): Performs calculations or operations, e.g.,
SUM(). - ColumnName: Refers to data columns, usually in the format
TableName[ColumnName]. - Operators: Arithmetic (+, -, *, /), comparison (=, >, <), and logical (AND, OR).
DAX
=SUM(Sales[Amount])
Example
This example creates a measure that calculates the total sales amount by summing the Amount column in the Sales table.
DAX
Total Sales = SUM(Sales[Amount])
Output
If Sales[Amount] has values 100, 200, 300, the measure returns 600
Common Pitfalls
Common mistakes when writing DAX formulas include:
- Forgetting the equal sign
=at the start. - Using incorrect column references without table names.
- Mixing calculated columns and measures incorrectly.
- Using aggregation functions where not needed.
Example of wrong and right formula:
DAX
Wrong: Total Sales = SUM(Amount) Right: Total Sales = SUM(Sales[Amount])
Quick Reference
| Element | Description | Example |
|---|---|---|
| Equal Sign | Starts every DAX formula | =SUM(Sales[Amount]) |
| Function | Performs calculation | SUM(), AVERAGE(), IF() |
| Column Reference | Refers to table column | Sales[Amount] |
| Operators | Arithmetic and logical | +, -, *, /, AND, OR |
| Measure | Dynamic calculation | Total Sales = SUM(Sales[Amount]) |
Key Takeaways
Always start your DAX formula with an equal sign (=).
Use full column references with table names like TableName[ColumnName].
Choose the right function for your calculation, such as SUM or IF.
Understand the difference between calculated columns and measures.
Check syntax carefully to avoid common errors like missing brackets or wrong references.