DAX quick reference
The sheet to keep open while you build a report. Every formula on it runs right here, against a real model.
How to use this
This is the sheet to keep open while you build a report. It is organised the way you actually reach for things — add something up, then split it, then compare it with last year — rather than alphabetically.
Everything here is written against the same small model, so you can try any formula immediately without inventing data. Nothing on this page is theory. If a formula is printed here, it has been run.
The model
One fact table and three dimensions — the shape most real Power BI models take.
48 sales rows across 2024 and 2025. Product holds six products in two categories, Region four regions with a manager each, and Calendar one row per day across both years.
Get the data and build it yourself
Reading DAX and writing DAX are different skills. Download the model, load it into Power BI Desktop, and type the formulas below into your own report — you should get exactly the numbers this page shows, because it is exactly the same data.
Loading it into Power BI Desktop
- Home → Get data → Excel workbook, pick the file, and tick all four sheets: Sales, Product, Region, Calendar. Click Load.
- Open Model view (the third icon down the left edge). Power BI will have guessed some relationships. Delete whatever it guessed — you want to build them yourself, and its guesses are often wrong.
- Drag Product[ProductID] onto Sales[ProductID]. Do the same for Region[RegionID] and for Calendar[Date] onto Sales[Date]. Each one should come out one-to-many, with the arrow pointing from the dimension towards Sales.
- Click the Calendar table, then Table tools → Mark as date table, and choose the Date column. Time intelligence will not work properly until you do this.
2025-03-06 can be read as text on an Indian regional setting. In Power Query, click the ABC icon on the Date column and choose Using locale → Date → English (United States). The .xlsx does not have this problem — the dates are already real dates in the file — which is why it is the one to prefer.Check you loaded it correctly
Before writing anything clever, write these three and compare. If any of them disagrees, the load went wrong, not the formula.
| Measure | You should get |
|---|---|
Total sales = SUM(Sales[Amount]) | 30,37,800 |
Number of orders = COUNTROWS(Sales) | 48 |
Days = COUNTROWS(Calendar) | 731 |
DATEADD, SAMEPERIODLASTYEAR and the rest do their job. A missing day is the most common reason time intelligence silently returns blank.1. Adding things up
These take a whole column and collapse it to one number. They are the ones you will type most.
| Function | Gives you |
|---|---|
SUM(table[col]) | The total |
AVERAGE(table[col]) | The mean, ignoring blanks |
MIN / MAX(table[col]) | Smallest and largest |
COUNT(table[col]) | How many rows have a value there |
COUNTROWS(table) | How many rows, full stop |
DISTINCTCOUNT(table[col]) | How many different values |
COUNTROWS(Sales) counts rows. COUNT(Sales[Amount]) counts rows where Amount is not blank. On clean data they agree; on real data they do not, and the gap is usually the thing you wanted to know about.2. Splitting it up
The same measure gives a different answer in every row of a visual. Nothing about the formula changes — the report supplies a different set of rows to each line.
Change Region[Region] in your head to Product[Category] and the same three measures answer a completely different question. That is the whole idea of a measure.
3. The X functions
An iterator goes down a table row by row, works something out on each row, and then aggregates the results. You need one whenever the calculation has to happen per row before it is totalled.
| Function | Does |
|---|---|
SUMX | Adds up the per-row results |
AVERAGEX | Averages them |
MINX / MAXX | Smallest and largest of them |
COUNTX | Counts the non-blank results |
SUM(Sales[Qty]) * SUM(Sales[Price]) multiplies two grand totals and produces a number that means nothing. SUMX(Sales, Sales[Qty] * Sales[Price]) multiplies within each row and then adds up, which is what you meant.It looks right, it returns a number, and nobody notices until a client checks it. When a calculation has a times or a divided by in it, ask whether it has to happen per row.
4. CALCULATE — the one that changes everything
CALCULATE evaluates an expression with the filters you give it. It is the most powerful function in DAX and the one that causes the most confusion, because of a single rule.
Forced South mentions Region, so it throws away the row’s own region and uses South instead — the same number all the way down. Computers here mentions Category, not Region, so each row keeps its own region and gets computers only. Read those two columns until the difference is obvious; it is the single most useful thing on this page.
Removing filters
ALL(Region) ignores whatever the row is filtering on that table, which is how you get a grand total to divide by. That pattern — a measure over the same measure with ALL — is how every percentage-of-total in Power BI is built.
More complicated conditions
A plain column = value filter cannot express “bigger than”. For that, filter a table with FILTER.
| Function | Use it to |
|---|---|
ALL(table) | Ignore filters on that table entirely |
FILTER(table, condition) | Build a filtered table for CALCULATE |
VALUES(table[col]) | The distinct values visible right now |
DISTINCT(table[col]) | The distinct values, blanks aside |
5. Decisions
| Function | Does |
|---|---|
IF(test, yes, no) | One decision |
SWITCH(TRUE(), test, result, ...) | Several, in order — far more readable than nested IFs |
AND / OR / NOT | Combine conditions |
ISBLANK(x) | True when there is nothing there |
BLANK() | Return nothing on purpose, so the row disappears from a visual |
DIVIDE(a, b) returns blank when b is zero. a / b throws an error that breaks the whole visual, not just that one cell. There is no situation where the slash is the better choice.DIVIDE(a, b, 0) if you want a zero instead of a blank.6. Comparing with last year
Time intelligence needs a proper date table — one row per day, no gaps, marked as the date table in the model. Without one these functions return wrong answers rather than errors, which is worse.
| Function | Gives you |
|---|---|
TOTALYTD(expr, dates) | Running total from 1 January |
SAMEPERIODLASTYEAR(dates) | The matching period a year back |
PREVIOUSYEAR(dates) | The whole of last year |
DATEADD(dates, n, unit) | Shift by any amount — the flexible one |
YEAR / MONTH / DAY(date) | Pull a part out of a date |
7. Reaching across a relationship
In a calculated column on the fact table, RELATED fetches a value from the dimension on the one side of the relationship.
Product[Category] without RELATED — the model does the work. RELATED is for calculated columns, where you are physically building a value into each row.8. Tidying the number
9. The traps that cost hours
| Symptom | What is actually wrong |
|---|---|
| The total is not the sum of the rows | Correct, and usually right. A measure is recalculated for the total row against all the data, not added up from the rows above. |
| Every row shows the same number | A CALCULATE filter is replacing the filter the row was supplying. |
| Multiplication comes out far too high | Two grand totals multiplied. You needed SUMX. |
| The whole visual errors | A division by zero. Use DIVIDE. |
| Time intelligence returns silly numbers | No proper date table, or it is not marked as one. |
| A measure will not sort | FORMAT turned it into text. |
| Blank rows appear in a visual | Rows in the fact table with no match in the dimension. |
10. Measure or calculated column?
| Measure | Calculated column | |
|---|---|---|
| Worked out | When the visual asks | Once, at refresh |
| Stored in the file | No | Yes — it makes the file bigger |
| Responds to slicers | Yes | No |
| Use it for | Anything you aggregate: totals, ratios, counts | Something you slice or group by |
When in doubt, write a measure. A calculated column that should have been a measure bloats the model and stops responding to filters. The reverse mistake is far less costly.