📢
Admissions Open | Free Career Counselling | Limited Scholarships
Register Now →

Learning Guides

Power BI DAX Functions Cheat Sheet (with Examples)

A Power BI DAX cheat sheet with 60+ functions and examples: CALCULATE, SUMX, time intelligence, window functions, UDFs, plus India-ready formulas.

You open Power BI, drag in your data, and build a chart in five minutes.

Then your manager asks for "sales this financial year vs last year, by region". And you're stuck staring at a blank formula bar.

This Power BI DAX cheat sheet gets you unstuck. It covers the DAX functions Power BI users need most, each with a formula you can copy, all on one simple sales model.

You'll also get:

  • The DAX features added since 2023 that older cheat sheets miss
  • India-ready formulas: April to March financial year, GST and lakh-style labels
  • The 10 DAX mistakes that break reports (and the fix for each)

Let's dive in.

Power BI DAX is the formula language you use to create measures, calculated columns and calculated tables in Power BI. DAX stands for Data Analysis Expressions, and it also runs in Analysis Services and Power Pivot in Excel. Everyday report work uses a small core of functions: aggregations, CALCULATE, iterators, time intelligence and a few logical and table functions.

Quick answer: power bi dax essentials (November 2026)

  • The core DAX function is CALCULATE, which changes the filter context a measure is evaluated in.
  • Measures calculate at report time for each visual cell; calculated columns calculate once per row at refresh and are stored in the model.
  • Iterators such as SUMX evaluate an expression row by row and then aggregate, which SUM alone cannot do.
  • Time intelligence functions such as TOTALYTD need a proper date table; for India's April to March year, set the year-end to "3/31".
  • DAX user-defined functions became generally available in the June 2026 Power BI release, so you can now write reusable functions.
Power BI DAX functions cheat sheet with function categories and examples
Function categories at a glance.

Contents

  1. The Sample Model Used in Every Example
  2. The DAX Ladder: Learn Functions in This Order
  3. Measures vs Calculated Columns
  4. Power BI DAX Aggregation Functions
  5. Iterator Functions (the X Functions)
  6. CALCULATE and Filter Functions in Power BI DAX
  7. Logical and Information Functions
  8. Time Intelligence DAX Functions
  9. Relationship, Table and Text Functions
  10. What's New in Power BI DAX
  11. DAX for Indian Reports: Fiscal Year, GST and Labels
  12. 10 Common DAX Mistakes and How to Fix Them
  13. FAQ

The Sample Model Used in Every Example

All the DAX formulas below run on one small sales model, so you can rebuild it and test each line. The model has four tables in a star schema:

TableKey columnsRole
SalesOrderID, OrderDate, ShipDate, CustomerID, ProductID, Qty, Price, AmountFact table (one row per order line)
ProductProductID, ProductName, CategoryDimension
CustomerCustomerID, Name, City, StateDimension
DateDate, Year, Month, FYDate table, marked as date table

Sales links to Product, Customer and Date (on OrderDate), much like the keys in SQL joins. A second, inactive relationship links Sales[ShipDate] to Date[Date], which you'll use with USERELATIONSHIP later.

Pro tip: Use the measure names from this page in your own model. Measures like [Total Sales] get reused inside almost every other formula, so name them clearly once.

Star schema sample model used for the DAX examples
One fact table, three dimensions: the shape most DAX formulas expect.

The DAX Ladder: Learn Functions in This Order

The DAX Ladder is a five-rung learning order so each group of functions builds on the last. Most people get stuck on DAX because they jump to time intelligence before they understand filter context.

  1. Aggregate: SUM, COUNTROWS, DISTINCTCOUNT, DIVIDE. Answer "how much?"
  2. Iterate: SUMX, AVERAGEX, RANKX. Answer "how much, row by row?"
  3. Filter: CALCULATE, ALL, ALLSELECTED, FILTER. Answer "how much, under these conditions?"
  4. Time: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD. Answer "how much, compared with when?"
  5. Shape: VAR, SWITCH, table functions. Answer "how do I keep this readable and reusable?"

Climbing one rung a week is a practical pace.

Measures vs Calculated Columns

A measure calculates at report time for each cell of a visual, while a calculated column calculates once per row during refresh and is stored in the model. Use measures for numbers you aggregate and slice; use calculated columns for values you filter, group or put on an axis.

MeasureCalculated column
Calculated whenEvery time a visual rendersAt data refresh
Stored in modelNo (only the formula)Yes (uses memory)
ContextFilter contextRow context
ExampleTotal Sales = SUM ( Sales[Amount] )Margin = Sales[Price] - Sales[Cost]
Use forKPIs, totals, ratiosSlicers, groups, labels

Rule of thumb: if you can write it as a measure, write it as a measure. Calculated columns make the file bigger.

Want more on this? It's one of the first questions in our Power BI interview questions guide.

Power BI DAX Aggregation Functions

Aggregation functions summarize a column in the current filter context. They're the first rung of the ladder and the building blocks for every other measure.

FunctionWhat it doesExample
SUMAdds a numeric columnTotal Sales = SUM ( Sales[Amount] )
AVERAGEMean of a columnAvg Price = AVERAGE ( Sales[Price] )
MIN / MAXSmallest or largest valueLast Order = MAX ( Sales[OrderDate] )
COUNTCounts non-blank numbersPriced Lines = COUNT ( Sales[Price] )
COUNTROWSCounts rows in a tableOrders = COUNTROWS ( Sales )
DISTINCTCOUNTCounts unique valuesCustomers = DISTINCTCOUNT ( Sales[CustomerID] )
DIVIDESafe division, handles zeroAvg Order Value = DIVIDE ( [Total Sales], [Orders] )

Use DIVIDE instead of the / operator. DIVIDE returns a blank (or a value you choose as the third argument) when the denominator is zero, so your visuals don't show errors.

Power BI KPI cards built with DAX aggregation functions
Four aggregation measures, four KPI cards.

Iterator Functions (the X Functions)

Iterator functions go through a table row by row, evaluate an expression for each row, then aggregate the results. You need them whenever the value doesn't exist as a single column, such as quantity times price.

FunctionWhat it doesExample
SUMXSum of a row-by-row expressionRevenue = SUMX ( Sales, Sales[Qty] * Sales[Price] )
AVERAGEXAverage of a row-by-row expressionAvg Line Value = AVERAGEX ( Sales, Sales[Qty] * Sales[Price] )
MAXX / MINXMax or min of an expressionBiggest Line = MAXX ( Sales, Sales[Qty] * Sales[Price] )
COUNTXCounts non-blank resultsLow-Price Lines = COUNTX ( FILTER ( Sales, Sales[Price] < 100 ), Sales[OrderID] )
RANKXRanks items by an expressionProduct Rank = RANKX ( ALL ( Product[ProductName] ), [Total Sales] )
CONCATENATEXJoins values into one textCategories = CONCATENATEX ( VALUES ( Product[Category] ), Product[Category], ", " )

Here's the key idea: SUM ( Sales[Qty] ) * SUM ( Sales[Price] ) multiplies two totals, which gives the wrong revenue. SUMX multiplies first, row by row, and then adds.

SUM vs SUMX example showing why iterators give the correct revenue
Multiply row by row first, then add. That's what SUMX does.

CALCULATE and Filter Functions in Power BI DAX

CALCULATE evaluates an expression in a modified filter context, and it's the single most important DAX function. Filters you pass on the same column replace the existing filter; filters on different columns combine with AND.

FunctionWhat it doesExample
CALCULATEChanges the filter contextSales MH = CALCULATE ( [Total Sales], Customer[State] = "Maharashtra" )
FILTERReturns a filtered tableBig Orders = CALCULATE ( [Orders], FILTER ( Sales, Sales[Amount] > 50000 ) )
ALLRemoves filters from a table or column% of Total = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Product ) ) )
ALLSELECTEDKeeps slicer filters, removes visual filters% of Selected = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( Product ) ) )
ALLEXCEPTRemoves all filters except listed columnsCategory Total = CALCULATE ( [Total Sales], ALLEXCEPT ( Product, Product[Category] ) )
REMOVEFILTERSClears filtersAll Cities = CALCULATE ( [Total Sales], REMOVEFILTERS ( Customer[City] ) )
KEEPFILTERSAdds a filter without overriding the current onePune Only = CALCULATE ( [Total Sales], KEEPFILTERS ( Customer[City] = "Pune" ) )
USERELATIONSHIPActivates an inactive relationshipSales by Ship Date = CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )
TREATASApplies values of one column as a filter on anotherTarget Sales = CALCULATE ( [Total Sales], TREATAS ( VALUES ( Targets[City] ), Customer[City] ) )

Two concepts make CALCULATE click:

  • Filter context is the set of filters coming from slicers, rows, columns and visual filters.
  • Context transition happens when CALCULATE runs inside row context (such as in a calculated column or an iterator). It turns the current row into a filter.

Note: Prefer simple Boolean filters like Customer[State] = "Maharashtra" over FILTER on a whole table. They're easier to read and usually faster.

CALCULATE and ALL changing filter context in a Power BI DAX matrix
ALL removes the product filter, so every row divides by the grand total.

Logical and Information Functions

Logical functions return different results based on conditions, and information functions test what's in the current context. Together they handle labels, bands and "what did the user select?" logic.

FunctionWhat it doesExample
IFTwo-way conditionTarget Met = IF ( [Total Sales] >= 1000000, "Yes", "No" )
SWITCHMany conditions, cleaner than nested IFSize Band = SWITCH ( TRUE (), Sales[Amount] >= 100000, "Large", Sales[Amount] >= 25000, "Medium", "Small" )
AND / OR, && / ||Combine conditionsKey Order = IF ( Sales[Amount] > 50000 && Sales[Qty] > 10, 1, 0 )
COALESCEFirst non-blank valueSales or Zero = COALESCE ( [Total Sales], 0 )
ISBLANKTests for blankNo Sales = ISBLANK ( [Total Sales] )
SELECTEDVALUESingle selected value, or a defaultCity Title = "Sales for " & SELECTEDVALUE ( Customer[City], "all cities" )
HASONEVALUETRUE if one value is in contextShow Rank = IF ( HASONEVALUE ( Product[ProductName] ), [Product Rank] )
ISINSCOPETRUE at a given hierarchy levelLevel = IF ( ISINSCOPE ( Product[ProductName] ), "Product", "Category" )

Use SWITCH ( TRUE (), ... ) any time you'd nest three or more IFs. Your future self will thank you.

Time Intelligence DAX Functions

Time intelligence functions compare values across periods, such as year to date or same period last year. They need a proper date table with one row per day, marked as a date table in Power BI.

First, create the date table (this one runs April 2024 to March 2027):

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 4, 1 ), DATE ( 2027, 3, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "FY", IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ) + 1, YEAR ( [Date] ) )
)

Then use these:

FunctionWhat it doesExample
TOTALYTDYear-to-date totalSales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
TOTALQTD / TOTALMTDQuarter or month to dateSales MTD = TOTALMTD ( [Total Sales], 'Date'[Date] )
SAMEPERIODLASTYEARSame dates, one year backSales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
DATEADDShift dates by an intervalSales PM = CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )
DATESINPERIODRolling window of datesSales 3M = CALCULATE ( [Total Sales], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ) )
DATESBETWEENDates between two datesDiwali Week = CALCULATE ( [Total Sales], DATESBETWEEN ( 'Date'[Date], DATE ( 2026, 11, 5 ), DATE ( 2026, 11, 11 ) ) )
PREVIOUSMONTHAll dates of the previous monthLast Month = CALCULATE ( [Total Sales], PREVIOUSMONTH ( 'Date'[Date] ) )
PARALLELPERIODFull shifted periodLast Year Full = CALCULATE ( [Total Sales], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )

And the measure every manager asks for, written with variables:

YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR LastYearSales = [Sales LY]
RETURN
    DIVIDE ( CurrentSales - LastYearSales, LastYearSales )

VAR and RETURN store a value once and reuse it. Your formula reads better and the engine doesn't calculate the same thing twice.

Time intelligence DAX chart comparing this year and last year sales
Illustrative: this year against last year by month, with FYTD and YoY Growth cards.

Relationship, Table and Text Functions

Relationship functions pull values across related tables, table functions return whole tables, and text functions clean up labels. You'll use them for lookups, top-N lists and readable titles.

FunctionCategoryExample
RELATEDRelationshipColumn in Sales: Category = RELATED ( Product[Category] )
RELATEDTABLERelationshipColumn in Customer: Order Count = COUNTROWS ( RELATEDTABLE ( Sales ) )
LOOKUPVALUERelationshipPrice List = LOOKUPVALUE ( PriceList[Price], PriceList[ProductID], Sales[ProductID] )
VALUES / DISTINCTTableCities = COUNTROWS ( VALUES ( Customer[City] ) )
TOPNTableTop 5 Sales = CALCULATE ( [Total Sales], TOPN ( 5, ALL ( Product[ProductName] ), [Total Sales] ) )
SUMMARIZECOLUMNSTableCity Summary = SUMMARIZECOLUMNS ( Customer[City], "Sales", [Total Sales] )
ADDCOLUMNSTableWith Rank = ADDCOLUMNS ( VALUES ( Product[ProductName] ), "Sales", [Total Sales] )
FORMATTextSales Label = FORMAT ( [Total Sales], "#,##0" )
LEFT / RIGHT / MIDTextState Code = LEFT ( Customer[State], 2 )
UPPER / LOWER / TRIMTextClean City = TRIM ( UPPER ( Customer[City] ) )
SEARCH / CONTAINSSTRINGTextIs Phone = CONTAINSSTRING ( Product[ProductName], "phone" )

Important: FORMAT returns text. Use it for titles and labels, never for a number you want to sort, sum or chart.

Building these into a full report? Our Power BI dashboard tutorial walks through it step by step.

What's New in Power BI DAX

Most older DAX cheat sheets stop before the newest features. Microsoft's DAX function reference now covers more than 250 functions (Microsoft Learn), so the list in this guide is a working subset, not the full reference.

User-defined functions

DAX user-defined functions (UDFs) let you package logic once and reuse it. They became generally available in Power BI Desktop and the Power BI Service with the June 2026 release, and need database compatibility level 1702 or higher (Microsoft Learn). In his 2025 year-end review, Marco Russo of SQLBI called UDFs the biggest change to the language since variables arrived in 2015 (SQLBI).

Visual calculations

A visual calculation is a DAX calculation defined on a visual instead of in the model. For example, Running Total = RUNNINGSUM ( [Total Sales] ) or Vs Previous = [Total Sales] - PREVIOUS ( [Total Sales] ). Use a visual calculation for a quick comparison on one visual; use a measure when several visuals need the same number.

Note: Check the current Microsoft Learn list of visual calculation functions before you rely on a specific one, since the list changes between releases.

DAX for Indian Reports: Fiscal Year, GST and Labels

Indian reports often need an April to March financial year, GST and figures shown in lakhs or crores. Here's how to handle each in DAX.

Financial year (April to March)

TOTALYTD takes an optional year-end date, so pass "3/31" for India's financial year:

Sales FYTD = TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" )

The FY column in the date table above labels April 2026 to March 2027 as FY 2027. Change the formula if your company labels it FY 2026-27.

GST-inclusive sales

A plain measure is the simplest way to add 18% GST:

Sales incl GST = [Total Sales] * 1.18

If the rate changes, update the one measure and every visual that uses it follows.

Crore labels

For a card label such as "₹3.00 Cr", a text measure is fine, since cards don't sort or chart:

Sales Cr Label = "₹" & FORMAT ( DIVIDE ( [Total Sales], 10000000 ), "0.00" ) & " Cr"

Use this only on cards and labels. Keep the numeric measure for anything you sort or chart.

Illustrative FY 2026-27 sales by region matrix with a GST-inclusive column
Illustrative mockup: April to March year, regional totals and GST at 18%.

10 Common DAX Mistakes and How to Fix Them

Most broken Power BI reports come from the same DAX mistakes. Check this list before you blame the data.

Checklist of common DAX mistakes and their fixes in Power BI
Five of the most common swaps. The full list of ten is below.
#MistakeFix
1Using / and getting errors on zeroUse DIVIDE ( a, b )
2SUM ( Qty ) * SUM ( Price ) for revenueUse SUMX ( Sales, Sales[Qty] * Sales[Price] )
3Calculated columns for every KPIWrite measures; keep columns for slicers and groups
4Time intelligence on the auto date tableBuild a date table and mark it as a date table
5TOTALYTD resetting on 31 December for an April to March yearAdd the "3/31" year-end argument
6FILTER over a whole fact table inside CALCULATEUse a column filter: Customer[State] = "Maharashtra"
7Using ALL when you meant "respect the slicer"Use ALLSELECTED
8Nested IFs five levels deepUse SWITCH ( TRUE (), ... )
9FORMAT on numbers you need to chartKeep the numeric measure; use FORMAT only for labels
10Repeating the same sub-calculationStore it once with VAR

Mistake 2 is the one interviewers love. If you can explain why SUMX and SUM give different revenue, you're ahead of many candidates. More practice questions are in our data analyst interview questions guide.

Power BI DAX: Your Next Step

Power BI DAX looks big, but a core set of functions and one idea, filter context, handle most business questions. Climb the DAX Ladder: aggregate, iterate, filter, time, then shape.

So pick one measure from each table above and rebuild it on your own data this week. Then try the Power BI projects for your resume to put the formulas to work.

Want structured practice with trainer feedback? Look at the Fireblaze Power BI course. Which Power BI DAX function gives you the most trouble right now?

FAQ

What is DAX in Power BI?

DAX (Data Analysis Expressions) is the formula language of Power BI, used to create measures, calculated columns and calculated tables. It also runs in Analysis Services and Power Pivot in Excel. DAX works on tables and columns, not cells, so Excel formulas like those in our IF, VLOOKUP and XLOOKUP guide can't be pasted in directly.

What are the most important Power BI DAX functions?

The most important Power BI DAX functions are CALCULATE, SUM, SUMX, DIVIDE, COUNTROWS, DISTINCTCOUNT, FILTER, ALL, ALLSELECTED, RELATED, SWITCH, SELECTEDVALUE, TOTALYTD, SAMEPERIODLASTYEAR and DATEADD. Learn CALCULATE and filter context first, because almost every advanced formula depends on them.

How many DAX functions are there?

Microsoft's DAX function reference covers more than 250 functions, including aggregation, filter, time intelligence, logical, text and table functions. You don't need all of them; a core set covers most business reports.

What is the difference between SUM and SUMX in DAX?

SUM adds the values of one column in the current filter context. SUMX goes row by row through a table, evaluates an expression for each row (such as quantity times price) and then adds the results. Use SUMX whenever the value you want to add doesn't exist as a single column.

Is DAX hard to learn?

DAX basics take a few days, but filter context and CALCULATE take most people a few weeks of practice. Learning in order (aggregations, iterators, CALCULATE, time intelligence, then variables and table functions) makes it much easier than jumping straight into complex formulas.

Are DAX user-defined functions available in Power BI now?

Yes. DAX user-defined functions became generally available in Power BI Desktop and the Power BI Service with the June 2026 release. Your model needs compatibility level 1702 or higher.

Sources

  1. Microsoft Learn: DAX function reference, 2026
  2. Microsoft Learn: DAX user-defined functions, 2026
  3. SQLBI: What happened in the DAX world in 2025, December 2025