Home › Guides › DAX Functions Cheat Sheet
Tech Explained · 2026DAX Functions Cheat Sheet 2026: 40 Formulas, Worked Examples and the Filter Context Rules Behind Most Errors
A DAX functions cheat sheet is a working reference to the formula language behind Power BI measures. Microsoft's DAX function reference documents over 250 functions, but roughly 40 cover nearly all production reporting: aggregators, iterators, CALCULATE and its filter modifiers, time intelligence, and a handful of table and text helpers.
- Forty functions carry the load. The rest are parent/child helpers, statistical distributions and legacy aliases you will never type.
- CALCULATE is the language. Learn what it does to filter context and the remaining 39 become arithmetic.
- SUM of a product is not the product of sums. The eight-row example below returns 1,53,400 with the wrong function and 16,850 with the right one.
- Boolean filters beat FILTER. Microsoft's best-practice page says to pass column predicates to CALCULATE, because Import tables are column stores tuned for that.
- Time intelligence fails silently. Without a contiguous date table covering whole years, SAMEPERIODLASTYEAR returns blank on every row.
- DAX moved in 2026. Microsoft dates TEXTCONTAINS and TEXTSIMILARITY to September 2026; user-defined functions remain in preview.
Your revenue card reads Rs 1,53,400. Finance says 16,850. Same file, same eight rows, and the only difference is which function you wrapped around the multiplication. That is how DAX usually goes wrong: no syntax error, no red squiggle, just a confident wrong number nobody questions for three weeks.
DAX Functions Cheat Sheet: The 12 You Will Type Every Day
The size of the language versus the size of the job
You do not need to learn DAX. You need about forty of it.
Functions documented in Microsoft's DAX function reference, of which the forty below cover the large majority of day-to-day Power BI reporting work.
Count stated on Microsoft's DAX function reference page, checked 12 November 2026.
Write these twelve from memory, say what each does to filter context, and you can read almost any measure you inherit.
| Function | What it does | Minimal example |
|---|---|---|
| SUM | Adds one column under the current filter context | SUM(Sales[Qty]) |
| SUMX | Walks a table row by row, then adds | SUMX(Sales, Sales[Qty] * Sales[UnitPrice]) |
| CALCULATE | Evaluates an expression under filters you specify | CALCULATE([Revenue], Sales[Region] = "South") |
| FILTER | Returns the rows that pass a test | FILTER(Sales, [Revenue] > 1000) |
| ALL | Removes filters from a table or columns | CALCULATE([Revenue], ALL(Sales)) |
| KEEPFILTERS | Intersects with existing filters instead of replacing | KEEPFILTERS(Sales[Region] = "South") |
| DIVIDE | Divides, with a chosen result instead of an error | DIVIDE([Revenue], [Target], 0) |
| COUNTROWS | Counts rows of any table expression | COUNTROWS(DISTINCT(Sales[OrderID])) |
| DISTINCTCOUNT | Counts unique values in a column | DISTINCTCOUNT(Sales[CustomerID]) |
| RELATED | Pulls a column from the one-side, inside row context | RELATED(Product[Category]) |
| SWITCH | Replaces nested IFs; with TRUE() reads like a case | SWITCH(TRUE(), [Rev] > 1e6, "A", "B") |
| VAR | A keyword, not a function: names a result once | VAR r = [Revenue] RETURN DIVIDE(r, 12) |
One opinion, held firmly: do not learn EARLIER. It solves a nested row context problem that VAR solves more legibly, and when it turns up in an inherited measure the first refactor should remove it. The trade-off: older forum answers assume you know it.
New to Power BI? Also read: Power BI tutorial for beginners, and 360DT's live Data Analyst course in Excel, SQL, Python and Power BI spends a module on measures with instructor review.
DAX Filter Context: The One Rule Behind Most Wrong Numbers
Two things decide what a measure returns. Filter context decides which rows exist at all. Row context exists only inside an iterator and points at one row. Almost everything people call "DAX being weird" is one of those two differing from what the author assumed.
How one Power BI measure is actually evaluated
The slicer sets the context, CALCULATE rewrites it, an iterator opens a row context, and context transition pushes that row back into the filter set.
Mechanism as documented across Microsoft's CALCULATE and filter-function reference pages, checked 12 November 2026.
Row context versus filter context, in eight rows
Paste this into Enter Data as a table called Sales. The correct revenue total is 16,850.
| Date | Region | Qty | UnitPrice | Row revenue |
|---|---|---|---|---|
| 2025-11-02 | South | 3 | 500 | 1,500 |
| 2025-11-18 | South | 2 | 500 | 1,000 |
| 2025-12-05 | North | 4 | 750 | 3,000 |
| 2026-01-11 | South | 5 | 500 | 2,500 |
| 2026-02-07 | South | 1 | 1,200 | 1,200 |
| 2026-02-22 | North | 3 | 750 | 2,250 |
| 2026-03-09 | South | 6 | 500 | 3,000 |
| 2026-03-30 | North | 2 | 1,200 | 2,400 |
Revenue (wrong) = SUM(Sales[Qty]) * SUM(Sales[UnitPrice])
-- SUM(Qty) = 26, SUM(UnitPrice) = 5900 => 1,53,400
Revenue = SUMX(Sales, Sales[Qty] * Sales[UnitPrice])
-- 1500+1000+3000+2500+1200+2250+3000+2400 => 16,850SUM has no row context. It collapses each column first, then multiplies: 26 units at a notional 5,900 rupees each. Nine times out, from one word, and that is why the X-suffixed functions exist.
CALCULATE Function in DAX: The Four Things It Does
CALCULATE applies your filters, replaces any existing filter on the same column unless told otherwise, performs context transition if a row context surrounds it, then evaluates. Every CALCULATE surprise traces to step two or three.
Percent of total shows it cleanly. South revenue above is 9,200 and North 7,650, so the denominator has to escape the region filter:
% of Total Revenue =
VAR RegionRev = [Revenue]
VAR AllRev = CALCULATE([Revenue], REMOVEFILTERS(Sales[Region]))
RETURN DIVIDE(RegionRev, AllRev)
-- South: 9200 / 16850 = 54.6% North: 7650 / 16850 = 45.4%Writing DAX formulas for Power BI that survive a slicer
Sliced to North, the first of these still returns 9,200, because your filter replaced the slicer. The report shows South numbers under a North heading.
South Revenue = CALCULATE([Revenue], Sales[Region] = "South")
South Revenue (safe) = CALCULATE([Revenue], KEEPFILTERS(Sales[Region] = "South"))The second intersects the filters, and since North and South do not overlap it correctly returns blank. What usually goes wrong is subtler than a wrong number: someone ships the unwrapped version, it looks fine because the test page had no slicer, and six months later a colleague adds one and three cards quietly start lying. Wrap single-column predicates in KEEPFILTERS by default.
There is a speed half too. Microsoft's best-practice page "Avoid using FILTER as a filter argument in DAX" says to pass Boolean expressions to CALCULATE wherever you can, because Import model tables are in-memory column stores optimised for column filtering, and to use FILTER only when necessary, such as an OR across columns.
-- preferred: a predicate the storage engine pushes down
CALCULATE([Revenue], Sales[Region] = "South")
-- slower: FILTER walks the table in the formula engine
CALCULATE([Revenue], FILTER(Sales, Sales[Region] = "South"))At eight rows, neither matters. For the two-person analytics team at a mid-size apparel exporter in Tiruppur, whose import model holds 1.8 million sales rows across eighteen categories, that is the gap between a visual that paints instantly and one that draws complaints. The modelling half sits in Also read: star schema explained; if the fact table comes from a lakehouse, a live Microsoft Fabric program covering DP-700 and DP-900 is the upstream job.
The Full 40: DAX Functions by Family
Where the forty functions sit
Filter control and table shaping take nearly half the sheet, which is where to spend your study time.
Counted from the forty functions in the table below, grouped 12 November 2026.
| Family | Functions | What to remember |
|---|---|---|
| Aggregators (6) | SUM, AVERAGE, MIN, MAX, COUNTROWS, DISTINCTCOUNT | No row context. They see filter context and nothing else. |
| Iterators (5) | SUMX, AVERAGEX, COUNTX, MAXX, RANKX | Open a row context over the first argument. Use when arithmetic happens per row. |
| Filter and context (9) | CALCULATE, FILTER, ALL, ALLSELECTED, ALLEXCEPT, REMOVEFILTERS, KEEPFILTERS, VALUES, HASONEVALUE | ALL removes everything, ALLEXCEPT keeps what you name, ALLSELECTED keeps the outer selection. Picking wrong is why percentages stop summing to 100. |
| Time intelligence (6) | DATESYTD, TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESINPERIOD, PREVIOUSMONTH | All need a contiguous date table, marked as one, covering whole years. No partial years. |
| Table, window, lookup (9) | SUMMARIZE, ADDCOLUMNS, SELECTCOLUMNS, OFFSET, INDEX, RELATED, RELATEDTABLE, LOOKUPVALUE, USERELATIONSHIP | RELATED needs a row context and goes to the one-side. OFFSET and INDEX removed most reasons to write self-joins. |
| Logic and safety (5) | IF, SWITCH, DIVIDE, ISBLANK, COALESCE | DIVIDE over the slash operator, SWITCH(TRUE(), ...) over nested IFs, COALESCE to turn blanks into zeros. |
On the window pair: Microsoft's reference states OFFSET returns a single row positioned before or after the current row, and INDEX returns a row at an absolute position within the specified partition, sorted by the specified order. The visual calculations overview maps PREVIOUS to OFFSET(-1) and FIRST to INDEX(1), worth knowing because answers online use both forms.
- A cheat sheet cannot teach evaluation context. Why a measure is right in a card and wrong in a matrix is a model problem, and no list fixes it.
- DAX is the wrong tool for row-level cleaning. Splitting strings, standardising dates, deduplicating keys: do it in Power Query, SQL or the lakehouse. A calculated column patching bad source data is a bug you own forever.
- Do not use DAX to hide a bad model. If a measure needs four nested CALCULATEs and a USERELATIONSHIP, the honest fix is usually one more dimension table.
Worked Example: Year-Over-Year on an Incomplete Date Range
Ten minutes in Power BI Desktop. Add a date table, mark it as one, relate it to Sales[Date], then write two more measures.
Date = ADDCOLUMNS(
CALENDAR(DATE(2025,1,1), DATE(2026,12,31)),
"Year", YEAR([Date]), "Month", FORMAT([Date], "MMM"))
Revenue LY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
Revenue YoY% = DIVIDE([Revenue] - [Revenue LY], [Revenue LY])Put Year and Month on rows. Revenue for 2026 is 11,350 across January to March, and Revenue LY is blank on every one of those rows. Not zero. Blank.
That is correct, and it is the lesson. The data starts in November 2025, so January to March 2025 has no rows, SAMEPERIODLASTYEAR returns an empty table, DIVIDE returns blank rather than an error, and the visual looks broken. It is not. Your date coverage is.
Two fixes, not interchangeable. With genuinely no prior-year history, suppress the comparison using IF(NOT ISBLANK([Revenue LY]), [Revenue YoY%]) so the column is empty on purpose. If the history exists but is not loading, extend the fact table. Picking the first when the problem is the second is how a report permanently hides a number somebody needed.
Common DAX Errors and the Fix for Each
| Symptom | Real cause | Fix |
|---|---|---|
| Rows are right, grand total is wrong | A ratio or product evaluated at the total grain, not per row | Rewrite with SUMX over the fact table, or push DIVIDE down to the right grain |
| "A single value for column cannot be determined" | A bare column reference in a measure, with no row context | Wrap it in SUM or MAX, or SELECTEDVALUE for the single selected value |
| Every year-over-year row is blank | Date table missing, unmarked, or not covering whole years | Build a CALENDAR date table, mark it, check the range spans the comparison period |
| One card ignores the slicer | CALCULATE overwrote the slicer's filter on that column | Wrap the predicate in KEEPFILTERS so the filters intersect |
| Correct number, twenty-second visual | FILTER passed as a CALCULATE filter argument on a large table | Pass a Boolean column predicate instead, per Microsoft's best-practice page |
| Percentages sum past 100 in a matrix | ALL removed the outer grouping filter as well | Use ALLSELECTED to respect the selection, or ALLEXCEPT to keep grouping columns |
Row five is the one that costs money. The 360DT PL-300 Power BI data analyst track runs performance diagnosis live, because reading about the column store is not the same as watching a measure drop from eight seconds to under one in Performance Analyzer. The free weekend webinars cover the rewrite workflow in ninety minutes.
Learn DAX with someone checking your measures, not a reference page
Ten weeks of live weekend classes covering Python, SQL, advanced Excel and Power BI, with preparation for the Microsoft PL-300 certification. Includes hands-on projects, mentor support and placement guidance.
Explore the course
How to Use This DAX Functions Cheat Sheet Without Memorising It
Nobody fluent in DAX recalls all forty. They recall which family to reach into, and what each rung buys them.
The four rungs of a DAX measure
Most people stall between rung two and rung three for a year. That is the rung worth drilling.
Rung 1
Rung 2
Rung 3
Rung 4
Author's recommended sequence, written against the DAX feature set available on 12 November 2026.
DAX time intelligence functions deserve their own week
Give the six their own session, because they share a dependency that is invisible in their signatures. Build the date table, mark it, verify a plain year-on-year measure on a known-good year, then write the fiscal variants. The other order debugs two unknowns at once.
What changed in DAX during 2026
Microsoft's New DAX functions page dates TEXTCONTAINS and TEXTSIMILARITY to September 2026, putting fuzzy text matching inside DAX rather than Power Query. User-defined functions, written with the FUNCTION keyword, shipped as a preview in the September 2025 Power BI update and remain in preview: enable them under File, Options and settings, Options, Preview features, and in a Power BI Project they live in a functions.tmdl file. Their names cannot collide with built-ins or reserved keywords such as measure or define. Visual calculations, which target a visual's result rather than the model, reportedly reached general availability in the May 2026 release.
Being blunt about that last one: visual calculations are a trap for learners. They make easy things trivial and hard things invisible, and anyone who starts there never learns filter context. Learn measures first.
On cost: third-party pricing trackers reading Microsoft's pricing page in October 2026 list Power BI Pro at $14 per user per month and Premium Per User at $24, both billed annually, after the April 2025 increases. Power BI Desktop is free, so every formula here runs on your own machine. You pay when you publish.
Should you let Copilot write your DAX?
For boilerplate, yes. For anything touching filter context, read every line. Generated measures reach for FILTER where a Boolean predicate would do, and will hand you an unwrapped CALCULATE that overwrites a slicer. If somebody has to own Copilot governance across Microsoft 365, the AB-900 Copilot and agent administrator track is the credential; teams building data agents on a semantic layer end up in RAG and AI agent engineering instead. See the certifications overview, and Also read: the 2026 Excel formulas cheat sheet.
What I Would Do With This Sheet
Starting DAX this week with thirty spare hours a month, I would ignore thirty-two of the forty for a fortnight. Write SUM, SUMX, CALCULATE, KEEPFILTERS, DIVIDE and VAR against a model small enough to check by hand, the way the eight-row table lets you. Then break things on purpose: drop a region slicer on the page and watch which measures lie. That table is a test harness, and knowing why 16,850 is right transfers straight to 1.8 million rows.
Then get someone to read your measures, the part a reference page cannot do. That is why the PL-300 data analyst course spends live hours on measure review rather than function lists. To see the style first, sit in on a free demo class and bring a measure giving you the wrong total.
Related guides
- Power BI Interview Questions 2026 the DAX interviewers expect on a whiteboard.
- PL-300 Certification Cost in 2026 what the exam costs in India, and whether it is worth the fee yet.
- Power BI Developer Salary in India 2026 what DAX fluency is actually worth in rupees, by experience.
- Power BI vs Excel in 2026 licence costs and the cases where a pivot table is still the right answer.
- SQL Commands Cheat Sheet 2026 the other half of an analyst's vocabulary.
- Data Analyst Jobs in Coimbatore 2026 who hires Power BI skills outside the metros.
Frequently asked questions
What is a DAX functions cheat sheet and how should I use it?
It is a grouped reference to the formula language behind Power BI measures, calculated columns and calculated tables. Use it by family, not alphabetically: when a number is wrong, ask whether the problem is aggregation, iteration, filter context or time intelligence, then look only there. Keep it open while you debug a real measure.
How many DAX functions do I actually need to learn?
Microsoft's DAX function reference documents over 250, and around forty cover the majority of reporting work. In your first fortnight you need six: SUM, SUMX, CALCULATE, KEEPFILTERS, DIVIDE and the VAR keyword. Parent/child functions, statistical distributions and legacy aliases can wait indefinitely.
What is the difference between SUM and SUMX in DAX?
SUM collapses one column to a single number under the current filter context and has no row context. SUMX opens a row context over a table, evaluates an expression per row, then adds. On the eight-row table above, SUM(Qty) times SUM(UnitPrice) returns 1,53,400 while SUMX returns the correct 16,850.
Why does my DAX measure ignore the slicer?
Because CALCULATE replaces any existing filter on the same column. If you wrote CALCULATE([Revenue], Sales[Region] = "South") and the user slices to North, your filter wins and the card shows South numbers. Wrap the predicate in KEEPFILTERS and the two filters intersect instead, which is almost always what you meant.
What is the difference between row context and filter context?
Filter context decides which rows exist at all, and comes from slicers, visual axes, page filters and CALCULATE. Row context exists only inside an iterator such as SUMX and points at one row at a time. RELATED needs a row context; SUM only sees filter context. Context transition is a measure reference converting the current row into a filter.
Do I need a paid Power BI licence to practise DAX?
No. Power BI Desktop is free and every formula here runs in it. Licences matter when you publish: third-party pricing trackers reading Microsoft's pricing page in October 2026 list Power BI Pro at $14 per user per month and Premium Per User at $24, both billed annually.
Can Copilot write DAX measures for me in 2026?
It handles boilerplate well and filter context poorly. Generated measures often use FILTER where a Boolean column predicate would be faster, and will hand you an unwrapped CALCULATE that silently overrides a slicer. Treat it as a first draft: check that a slicer still affects the result, and that the grand total matches the sum of the rows.
About this guide. 360 Digital Transformation is an Anthropic Partner and a Microsoft Authorized Training Partner. Other certification bodies, vendors and employers named here are not affiliated with us. Tools and versions change quickly; commands and figures cited were checked on 12 November 2026.
Microsoft Excel
Python
