Power BI DAX Essential Functions — Explained with Examples

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.