DAX, short for Data Analysis Expressions, is the formula language of Power BI. Most reports need only about a dozen formulas: SUM and DISTINCTCOUNT for totals, DIVIDE for ratios, CALCULATE to change filters, SAMEPERIODLASTYEAR and TOTALYTD for time comparisons, ALL for percentage of total, and RANKX for rankings. Learn those and you can handle most business questions.
DAX looks like Excel formulas but behaves differently, because it works on whole columns and responds to the filters on the page. If you are new to the tool, start with what Power BI is and our first-week plan.
What Is the Difference Between a Measure and a Calculated Column?
A calculated column is computed row by row when data loads and is stored in the model. A measure is computed on the fly when a visual needs it and responds to filters. Rule of thumb: if you want to sum, average, count or divide, write a measure. If you need a new value on every row, such as a category label, use a column. Measures keep the file smaller and the answers correct when filters change.
What Are Row Context and Filter Context?
Row context means “the current row”, as in a calculated column. Filter context means “the filters applied right now”, from slicers, rows and columns of a visual. A measure like SUM(Sales[Amount]) returns a different answer in each bar because each bar has a different filter context. Understanding this one idea clears up most DAX confusion. A small way to remember it: filter context is what the viewer has selected, and row context is the pointer in a table.
The 12 DAX Formulas Most Analysts Use
The examples assume a Sales table with Amount, OrderID and CustomerID, a Product table, and a Date table.
| No. | Goal | DAX example |
|---|---|---|
| 1 | Total sales | Total Sales = SUM(Sales[Amount]) |
| 2 | Unique orders | Orders = DISTINCTCOUNT(Sales[OrderID]) |
| 3 | Safe ratio | AOV = DIVIDE([Total Sales],[Orders]) |
| 4 | Simple condition | Status = IF([Total Sales]>=[Target],"On track","Behind") |
| 5 | Many conditions | Band = SWITCH(TRUE(),[Total Sales]>=1000000,"High",[Total Sales]>=500000,"Mid","Low") |
| 6 | Change a filter | North Sales = CALCULATE([Total Sales],Region[Name]="North") |
| 7 | Share of total | Share = DIVIDE([Total Sales],CALCULATE([Total Sales],ALL(Product))) |
| 8 | Same period last year | Sales LY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date])) |
| 9 | Year to date | Sales YTD = TOTALYTD([Total Sales],'Date'[Date]) |
| 10 | Shift dates | Sales Last Month = CALCULATE([Total Sales],DATEADD('Date'[Date],-1,MONTH)) |
| 11 | Rank items | Rank = RANKX(ALL(Product[Name]),[Total Sales]) |
| 12 | Variables | Margin % = VAR Profit = [Total Sales]-[Total Cost] RETURN DIVIDE(Profit,[Total Sales]) |
How Does CALCULATE Work?
CALCULATE evaluates an expression in a modified filter context. The first argument is what to calculate, and the next arguments are the filters to apply. CALCULATE([Total Sales], Region[Name]="North") ignores the page’s region choice and forces North. It is the most important function in DAX, and once you are comfortable with it, ALL, FILTER and time intelligence all start to make sense.
A common beginner error is using FILTER on a whole table when a simple column condition would do. Prefer the simple form, which is faster and clearer.
How Do You Calculate Year-on-Year Growth?
Build it in steps so you can test each part.
| Step | Measure |
|---|---|
| 1 | Total Sales = SUM(Sales[Amount]) |
| 2 | Sales LY = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date])) |
| 3 | YoY % = DIVIDE([Total Sales]-[Sales LY],[Sales LY]) |
These formulas need a date table with continuous dates, marked as a date table in the model. If your year-on-year column shows blank, check that the date table and relationship are set up correctly. Our data deduplication tool guide is useful when duplicate rows are inflating your totals.
Which DAX Mistakes Cause the Most Trouble?
| Mistake | Symptom | Fix |
|---|---|---|
| Using the slash sign instead of DIVIDE | Error or infinity when the denominator is zero | Use DIVIDE |
| No date table | Time intelligence returns blanks | Create and mark a date table |
| Calculated columns for totals | Wrong totals when filtered | Use a measure |
| Hard-coded numbers | Formula breaks next year | Use a parameter or a column |
| Filtering a whole table | Slow, hard-to-read formula | Filter a single column |
| Many-to-many relationships | Unexpected totals | Rework the model to a star schema |
How Can You Learn DAX Faster?
Write one formula a day on your own data, and always check the answer against Excel. Use DAX Query View in Power BI Desktop to run quick queries. Read Microsoft’s DAX reference and the free learning path, then try the formulas used by your team. Copilot and other AI helpers can draft DAX, but you should still understand the logic to catch errors; see our look at AI and Copilot in BI dashboards. Our guide to the best Power BI courses and certifications lists structured options.





