DAX (Data Analysis Expressions) is the formula language that powers calculations in Power BI, Excel Power Pivot, and SQL Server Analysis Services. Mastering DAX is what separates a dashboard builder from a true analyst.
This cheat sheet gives you the essential DAX functions you need, organized by category, with clear syntax and real-world examples you can copy and adapt.
Quick Reference: DAX Function Categories
| Category | Key Functions | When to Use |
|---|---|---|
| Aggregation | SUM, AVERAGE, COUNT, MIN, MAX | Basic totals and summaries |
| Iterator | SUMX, AVERAGEX, MAXX, MINX, COUNTX | Row-by-row calculations |
| Logical | IF, SWITCH, AND, OR, NOT | Conditional logic and branching |
| Text | CONCATENATE, LEFT, RIGHT, MID, LEN, UPPER, LOWER | String manipulation |
| Date & Time | TODAY, YEAR, MONTH, DAY, DATEDIFF, DATEADD | Date calculations |
| Filter | CALCULATE, FILTER, ALL, ALLEXCEPT, KEEPFILTERS | Modify filter context |
| Time Intelligence | TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD | Year-over-year and period comparisons |
| Relationship | RELATED, RELATEDTABLE | Pull data across relationships |
| Table | DISTINCT, VALUES, SELECTCOLUMNS, ADDCOLUMNS | Create or modify tables |
| Ranking | RANKX | Rank values within a group |
1. Aggregation Functions
The foundation of any DAX measure.
SUM
Total Sales = SUM(Sales[Amount])
AVERAGE
Average Order Value = AVERAGE(Orders[OrderTotal])
COUNT / COUNTA / COUNTROWS
Transaction Count = COUNTROWS(Transactions)
Non-Blank Count = COUNTA(Customers[Email])
MIN / MAX
First Order Date = MIN(Orders[OrderDate])
Highest Sale = MAX(Sales[Amount])
Tip:
Aggregation functions are the building blocks of almost every Power BI report. Learn these before moving to more advanced DAX functions.
2. Iterator Functions (X-Functions)
Iterators evaluate an expression row-by-row across a table, then aggregate the results.
SUMX
Total Revenue =
SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)
AVERAGEX
Avg Line Total =
AVERAGEX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)
MAXX / MINX
Max Line Value =
MAXX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)
RANKX
Product Rank =
RANKX(
ALL(Products[ProductName]),
[Total Sales],
,
DESC
)
Pro Tip:
Iterators are more flexible than basic aggregations but can be slower on large tables. Use them when you need row-level logic before aggregation.
Iterators are more flexible than basic aggregations but can be slower on large tables. Use them when you need row-level logic before aggregation.
3. Logical Functions
IF
Sales Category =
IF(
[Total Sales] > 10000,
"High",
"Low"
)
SWITCH (Multiple Conditions)
Performance Rating =
SWITCH(
TRUE(),
[Total Sales] > 50000, "Excellent",
[Total Sales] > 20000, "Good",
[Total Sales] > 5000, "Average",
"Below Target"
)
AND / OR / NOT
High Priority =
IF(
AND(
[Total Sales] > 10000,
[Profit Margin] < 0.1
),
"Yes",
"No"
)
Best Practice:
For multiple conditions, SWITCH(TRUE()) is usually cleaner and easier to maintain than nesting several IF statements.
For multiple conditions, SWITCH(TRUE()) is usually cleaner and easier to maintain than nesting several IF statements.
4. Text Functions
CONCATENATE / CONCATENATEX
Full Name =
CONCATENATE(
Customers[FirstName],
" " & Customers[LastName]
)
Customer List =
CONCATENATEX(
Customers,
Customers[FirstName],
", "
)
LEFT / RIGHT / MID
First 3 Chars =
LEFT(Customers[Email],3)
Last 3 Chars =
RIGHT(Customers[Email],3)
Middle Part =
MID(Customers[Email],6,4)
LEN / UPPER / LOWER / REPLACE
Email Length =
LEN(Customers[Email])
Upper Case =
UPPER(Customers[LastName])
Lower Case =
LOWER(Customers[LastName])
Replace Text =
REPLACE(
Customers[Email],
1,
4,
"****"
)
SEARCH / FIND
Position of @ =
SEARCH(
"@",
Customers[Email]
)
Tip:
Text functions are especially useful for cleaning imported datasets before building reports and dashboards.
Text functions are especially useful for cleaning imported datasets before building reports and dashboards.
5. Date & Time Functions
Date calculations are essential for trend analysis, reporting periods, and time intelligence in Power BI.
TODAY / NOW
Current Date =
TODAY()
Current DateTime =
NOW()
YEAR / MONTH / DAY / WEEKDAY
Order Year =
YEAR(Orders[OrderDate])
Order Month =
MONTH(Orders[OrderDate])
Day of Week =
WEEKDAY(
Orders[OrderDate],
2
)
DATEDIFF
Days Since Order =
DATEDIFF(
Orders[OrderDate],
TODAY(),
DAY
)
Months Since Order =
DATEDIFF(
Orders[OrderDate],
TODAY(),
MONTH
)
DATEADD
Sales Last Month =
CALCULATE(
[Total Sales],
DATEADD('Date'[Date],-1,MONTH)
)
Sales Last Year =
CALCULATE(
[Total Sales],
DATEADD('Date'[Date],-1,YEAR)
)
Tip:
Always create a proper Date table before using DATEADD or any Time Intelligence function. Without a continuous Date table, many DAX time calculations won't work correctly.
Always create a proper Date table before using DATEADD or any Time Intelligence function. Without a continuous Date table, many DAX time calculations won't work correctly.
6. Filter Functions
Filter functions allow you to modify filter context, which is one of the most important concepts in DAX.
CALCULATE (The Most Important DAX Function)
Sales Amount =
SUM(Sales[Amount])
Sales West Region =
CALCULATE(
[Sales Amount],
Customers[Region] = "West"
)
Sales All Regions =
CALCULATE(
[Sales Amount],
ALL(Customers[Region])
)
Important:
If you master only one DAX function, make it CALCULATE(). Nearly every advanced Power BI model relies on it because it changes the filter context of a calculation.
If you master only one DAX function, make it CALCULATE(). Nearly every advanced Power BI model relies on it because it changes the filter context of a calculation.
FILTER
High Value Sales =
CALCULATE(
[Sales Amount],
FILTER(
Sales,
Sales[Amount] > 1000
)
)
ALL / ALLEXCEPT / ALLSELECTED
All Sales =
CALCULATE(
[Sales Amount],
ALL(Sales)
)
Sales All Except Category =
CALCULATE(
[Sales Amount],
ALLEXCEPT(
Sales,
Sales[Category]
)
)
Sales Current Selection =
CALCULATE(
[Sales Amount],
ALLSELECTED(Sales)
)
KEEPFILTERS
Sales With KeepFilters =
CALCULATE(
[Sales Amount],
KEEPFILTERS(
Customers[Region] = "West"
)
)
Best Practice:
Whenever possible, use direct column filters inside CALCULATE() instead of wrapping an entire table inside FILTER(). It usually performs better and is easier to read.
Whenever possible, use direct column filters inside CALCULATE() instead of wrapping an entire table inside FILTER(). It usually performs better and is easier to read.
7. Time Intelligence Functions
Time Intelligence functions make it easy to compare periods such as Month-to-Date (MTD), Quarter-to-Date (QTD), Year-to-Date (YTD), and Year-over-Year (YoY).
TOTALYTD / TOTALMTD / TOTALQTD
Sales YTD =
TOTALYTD(
[Sales Amount],
'Date'[Date]
)
Sales MTD =
TOTALMTD(
[Sales Amount],
'Date'[Date]
)
Sales QTD =
TOTALQTD(
[Sales Amount],
'Date'[Date]
)
DATESYTD / DATESMTD / DATESQTD
Sales YTD Alt =
CALCULATE(
[Sales Amount],
DATESYTD('Date'[Date])
)
SAMEPERIODLASTYEAR
Sales Last Year =
CALCULATE(
[Sales Amount],
SAMEPERIODLASTYEAR('Date'[Date])
)
DATEADD (Flexible Time Comparison)
Sales Previous Month =
CALCULATE(
[Sales Amount],
DATEADD('Date'[Date],-1,MONTH)
)
Sales Previous Quarter =
CALCULATE(
[Sales Amount],
DATEADD('Date'[Date],-1,QUARTER)
)
Sales Previous Year =
CALCULATE(
[Sales Amount],
DATEADD('Date'[Date],-1,YEAR)
)
Month-over-Month Growth %
MoM Growth % =
DIVIDE(
[Sales Amount] -
CALCULATE(
[Sales Amount],
DATEADD('Date'[Date],-1,MONTH)
),
CALCULATE(
[Sales Amount],
DATEADD('Date'[Date],-1,MONTH)
),
0
)
Tip:
Time Intelligence functions require a continuous Date table that is marked as the official Date Table in Power BI.
Time Intelligence functions require a continuous Date table that is marked as the official Date Table in Power BI.
8. Relationship Functions
RELATED
Customer Region =
RELATED(Customers[Region])
RELATEDTABLE
Order Count =
COUNTROWS(
RELATEDTABLE(Orders)
)
Tip:
Use RELATED() when you need a value from a related table, and RELATEDTABLE() when you need all related rows.
Use RELATED() when you need a value from a related table, and RELATEDTABLE() when you need all related rows.
9. Table Functions
DISTINCT / VALUES
Unique Products =
DISTINCT(
Products[ProductName]
)
Unique Customers =
VALUES(
Customers[CustomerID]
)
SELECTCOLUMNS
Selected Columns =
SELECTCOLUMNS(
Products,
"Product", Products[ProductName],
"Category", Products[Category],
"Price", Products[UnitPrice]
)
ADDCOLUMNS
Product With Margin =
ADDCOLUMNS(
Products,
"Margin",
Products[UnitPrice] -
Products[UnitCost]
)
CALENDAR / CALENDARAUTO
Date Table =
CALENDAR(
DATE(2024,1,1),
DATE(2026,12,31)
)
Auto Date Table =
CALENDARAUTO()
10. Calculated Columns vs. Measures
Calculated Column (Row Context)
Profit =
Sales[Revenue] -
Sales[Cost]
- Computed once per row during data refresh
- Stored in the model (uses memory)
- Best for static row-level calculations
Measure (Filter Context)
Total Profit =
SUMX(
Sales,
Sales[Revenue] -
Sales[Cost]
)
- Computed dynamically based on filters
- Does not consume model storage
- Ideal for reports and dashboards
Remember:
Whenever possible, use a Measure instead of a Calculated Column. Measures are more memory efficient and respond dynamically to slicers and filters.
Whenever possible, use a Measure instead of a Calculated Column. Measures are more memory efficient and respond dynamically to slicers and filters.
Understanding Context: The Key to DAX
Filter Context
Filter context is created by visuals, slicers, or DAX functions like CALCULATE().
Total Revenue =
SUM(Sales[Revenue])
- Returns different values depending on report filters.
- Changes automatically when slicers or visuals are applied.
Row Context
Row context is created by iterators (X-functions) or calculated columns.
Line Total =
SUMX(
Sales,
Sales[Quantity] *
Sales[UnitPrice]
)
- Evaluates one row at a time.
- Then aggregates the results.
Most Important Concept:
Understanding the difference between Row Context and Filter Context is the biggest step toward mastering DAX.
Understanding the difference between Row Context and Filter Context is the biggest step toward mastering DAX.
11. Performance Tips
- Prefer Measures over Calculated Columns whenever possible.
- Use CALCULATE() instead of wrapping large tables inside FILTER.
- Avoid unnecessary calculated columns.
- Use DISTINCTCOUNT() instead of COUNTROWS(DISTINCT()).
- Create and maintain a dedicated Date table.
Key Takeaways
- Master CALCULATE first — it's the most powerful and frequently used DAX function.
- Understand Filter Context vs. Row Context — this is the foundation of writing correct DAX.
- Use X-functions whenever row-by-row calculations are needed before aggregation.
- Create a proper Date Table before using Time Intelligence functions.
- Prefer Measures over Calculated Columns for better performance and flexibility.
- Practice regularly by building real reports instead of memorizing syntax.
Frequently Asked Questions
What is DAX used for?
DAX (Data Analysis Expressions) is the formula language used in Power BI, Excel Power Pivot, and SQL Server Analysis Services. It is used to create measures, calculated columns, calculated tables, and advanced business calculations.
What's the difference between a Calculated Column and a Measure?
A Calculated Column is evaluated during data refresh and stored in the data model. A Measure is calculated dynamically based on the current filter context and does not consume additional storage.
What is Filter Context?
Filter Context is the collection of filters applied by visuals, slicers, page filters, report filters, or DAX functions like CALCULATE().
What is Row Context?
Row Context means DAX evaluates one row at a time. It exists naturally inside Calculated Columns and Iterator functions like SUMX(), MAXX(), and AVERAGEX().
How do I calculate Year-over-Year Growth?
YoY Growth % =
DIVIDE(
[Total Sales] -
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('Date'[Date])
),
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('Date'[Date])
),
0
)
What is CALCULATE()?
CALCULATE() evaluates an expression under a modified filter context. It is widely considered the most important function in DAX.
Sales West =
CALCULATE(
[Total Sales],
Customers[Region]="West"
)
How do I create a Date Table?
Date Table =
ADDCOLUMNS(
CALENDARAUTO(),
"Year",YEAR([Date]),
"Month",MONTH([Date]),
"Month Name",FORMAT([Date],"MMMM"),
"Quarter","Q"&QUARTER([Date]),
"Day",DAY([Date])
)
Why should I use SUMX instead of SUM?
Use SUMX whenever each row requires a calculation before the final aggregation. For example, multiplying Quantity by Unit Price for every row before calculating Total Revenue.
Total Revenue =
SUMX(
Sales,
Sales[Quantity] *
Sales[UnitPrice]
)
Related Articles
- Power BI DAX cheat sheet Free Download
- SQL for Finance Teams: Pulling Data from ERP and Accounting Systems
- Data Analyst vs. Business Analyst: What's the Difference?
- The Best Data Analyst Portfolio Examples to Copy (2026 Guide)
External References
- Microsoft Learn – DAX (Data Analysis Expressions) Overview
- SQLBI – DAX Guide
- Enterprise DNA – DAX Patterns & Best Practices
About This Guide
Examples in this guide follow standard DAX syntax compatible with Microsoft Power BI Desktop and Power BI Service. Minor syntax differences may exist in Excel Power Pivot and SQL Server Analysis Services.
Last Updated: July 2026
){kind=link}
0 Comments