If you’ve ever struggled with CALCULATE() or wondered why SUMX() behaves differently from SUM(), this guide is for you.
DAX (Data Analysis Expressions) is the language that powers Power BI, Analysis Services, and Power Pivot — enabling dynamic calculations, filtering, and time intelligence.
Below is a categorized cheat sheet of essential DAX functions, plus examples showing how to use each in real-world Power BI scenarios.
Filtering & Context
These functions control how filters are applied and evaluated in your calculations.
| Function |
Example |
Description |
CALCULATE() |
CALCULATE(SUM(Sales[Amount]), Region[Name] = "Nairobi") |
Changes filter context to calculate total sales for Nairobi. |
FILTER() |
FILTER(Sales, Sales[Amount] > 10000) |
Returns a table filtered by condition. |
ALL() |
CALCULATE(SUM(Sales[Amount]), ALL(Region)) |
Ignores filters on Region. |
REMOVEFILTERS() |
CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Region)) |
Removes filters from Region. |
VALUES() |
VALUES(Customer[City]) |
Returns unique list of cities. |
SELECTEDVALUE() |
SELECTEDVALUE(Product[Category], "All") |
Returns selected category or “All” if none. |
TREATAS() |
TREATAS(VALUES(Temp[City]), Customer[City]) |
Applies one table’s values as filters on another. |
KEEPFILTERS() |
CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Product[Category] = "Electronics")) |
Keeps existing filters and adds new ones. |
ALLSELECTED() |
CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Region)) |
Respects user selections in visuals. |
ALLEXCEPT() |
CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Year])) |
Removes all filters except Year. |
Aggregation
Summarize or aggregate data across rows or columns.
| Function |
Example |
Description |
SUM() |
SUM(Sales[Amount]) |
Adds all sales amounts. |
AVERAGE() |
AVERAGE(Sales[Amount]) |
Calculates mean value. |
COUNT() |
COUNT(Customer[ID]) |
Counts non-blank entries. |
COUNTROWS() |
COUNTROWS(Sales) |
Counts rows in a table. |
DISTINCTCOUNT() |
DISTINCTCOUNT(Customer[ID]) |
Counts unique customers. |
MIN() |
MIN(Sales[Amount]) |
Finds smallest sale. |
MAX() |
MAX(Sales[Amount]) |
Finds largest sale. |
Iterator (X) Functions
Perform row-by-row calculations before aggregation.
| Function |
Example |
Description |
SUMX() |
SUMX(Sales, Sales[Quantity] * Sales[Price]) |
Calculates total revenue per row, then sums. |
AVERAGEX() |
AVERAGEX(Products, Products[ProfitMargin]) |
Averages profit margins across products. |
MINX() |
MINX(Orders, Orders[DeliveryDays]) |
Finds minimum delivery days. |
MAXX() |
MAXX(Orders, Orders[DeliveryDays]) |
Finds maximum delivery days. |
Logical Functions
Control flow and conditional logic.
| Function |
Example |
Description |
IF() |
IF(Sales[Amount] > 10000, "High", "Low") |
Returns “High” or “Low” based on condition. |
SWITCH() |
SWITCH(TRUE(), Sales[Amount] > 10000, "High", Sales[Amount] > 5000, "Medium", "Low") |
Multi-condition logic. |
COALESCE() |
COALESCE(Sales[Discount], 0) |
Replaces blanks with default value. |
Relationship Functions
Work across related tables.
| Function |
Example |
Description |
RELATED() |
RELATED(Customer[Name]) |
Fetches related customer name. |
LOOKUPVALUE() |
LOOKUPVALUE(Customer[Email], Customer[ID], Sales[CustomerID]) |
Finds email based on ID. |
USERELATIONSHIP() |
CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date])) |
Activates inactive relationship. |
Ranking Functions
Rank or sort data dynamically.
| Function |
Example |
Description |
RANKX() |
RANKX(ALL(Customer), SUM(Sales[Amount]), , DESC) |
Ranks customers by total sales. |
TOPN() |
TOPN(5, Sales, Sales[Amount], DESC) |
Returns top 5 sales records. |
Mathematical Functions
| Function |
Example |
Description |
DIVIDE() |
DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0) |
Safe division avoiding errors. |
ROUND() |
ROUND(Sales[Amount], 2) |
Rounds to two decimals. |
Date & Time Functions
| Function |
Example |
Description |
TODAY() |
TODAY() |
Returns current date. |
NOW() |
NOW() |
Returns current date and time. |
DATEDIFF() |
DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY) |
Calculates days between two dates. |
Time Intelligence
| Function |
Example |
Description |
DATEADD() |
DATEADD(Calendar[Date], -1, YEAR) |
Shifts date context by one year. |
SAMEPERIODLASTYEAR() |
SAMEPERIODLASTYEAR(Calendar[Date]) |
Compares same period last year. |
TOTALYTD() |
TOTALYTD(SUM(Sales[Amount]), Calendar[Date]) |
Year-to-date total. |
DATESBETWEEN() |
DATESBETWEEN(Calendar[Date], DATE(2026,1,1), DATE(2026,6,30)) |
Filters dates between range. |
DATESINPERIOD() |
DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -30, DAY) |
Last 30 days. |
Table Functions
| Function |
Example |
Description |
ADDCOLUMNS() |
ADDCOLUMNS(Sales, "Profit", Sales[Revenue] - Sales[Cost]) |
Adds calculated column. |
SUMMARIZE() |
SUMMARIZE(Sales, Region[Name], "TotalSales", SUM(Sales[Amount])) |
Groups and summarizes data. |
SUMMARIZECOLUMNS() |
SUMMARIZECOLUMNS(Region[Name], "TotalSales", SUM(Sales[Amount])) |
Similar to SUMMARIZE but optimized. |
UNION() |
UNION(TableA, TableB) |
Combines two tables. |
INTERSECT() |
INTERSECT(TableA, TableB) |
Returns common rows. |
EXCEPT() |
EXCEPT(TableA, TableB) |
Returns rows in A not in B. |
Text Functions
| Function |
Example |
Description |
LEFT() |
LEFT(Customer[Name], 3) |
First three letters. |
RIGHT() |
RIGHT(Customer[Name], 3) |
Last three letters. |
SEARCH() |
SEARCH("Ltd", Company[Name], 1, -1) |
Finds position of substring. |
CONCATENATEX() |
CONCATENATEX(Customer, Customer[Name], ", ") |
Joins names with commas. |
Information Functions
| Function |
Example |
Description |
ISBLANK() |
IF(ISBLANK(Sales[Amount]), 0, Sales[Amount]) |
Checks for blank values. |
HASONEVALUE() |
IF(HASONEVALUE(Product[Category]), VALUES(Product[Category]), "Multiple") |
Detects single selection. |
These functions form the foundation of every Power BI model.
Mastering them means you can build dynamic dashboards, automate KPIs, and handle complex business logic with ease.
Tip: Start with CALCULATE(), FILTER(), and SUMX() — they’re the most powerful trio in DAX.
Source
Microsoft DAX Documentation
Essential DAX functions for building powerful Power BI reports and models.
Top comments (0)