Direct Answer: What are the most important DAX functions in Power BI?
The most important DAX functions are: 1) CALCULATE (modifies filter context); 2) FILTER (returns a filtered table); 3) ALL & ALLEXCEPT (removes active filters); 4) SUMX & AVERAGEX (row-by-row iterators); 5) SAMEPERIODLASTYEAR, DATEADD, and TOTALYTD (Time Intelligence); 6) DIVIDE (safe division without zero-divide errors); 7) RELATED (fetches related dimension values).
DAX (Data Analysis Expressions) is the formula language used to create custom tables, columns, and calculated measures inside **Microsoft Power BI, Analysis Services, and Excel Power Pivot**. While creating basic visual charts in Power BI is simple, writing advanced DAX formulas is what defines a true Business Intelligence professional.
This tutorial details the 25 most critical DAX formulas, categorized by their calculation purpose.
Category 1: The Engine (Filter Context Modification)
1. CALCULATE
The single most powerful function in DAX. It evaluates an expression under a modified filter context.
TotalRetailSales = CALCULATE(SUM(Sales[Revenue]), Sales[Segment] == "Retail")2. FILTER
Returns a table representing a subset of another table based on logical conditions. Used inside `CALCULATE` when filtering measures.
HighValueSales = CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Revenue] > 5000))3. ALL
Returns all rows in a table or column, completely ignoring any active slicer or row filters. Useful for calculating percentage shares.
AllSalesRevenue = CALCULATE(SUM(Sales[Revenue]), ALL(Sales))4. ALLEXCEPT
Removes all filters in the table except filters on specified columns.
SalesExceptCategory = CALCULATE(SUM(Sales[Revenue]), ALLEXCEPT(Sales, Sales[ProductCategory]))Category 2: Iteration Functions (X-Functions)
Standard aggregates (like `SUM` or `AVERAGE`) operate on columns. Iterator functions end in "X" and evaluate calculations row-by-row before aggregating the final totals.
5. SUMX
TotalProfit = SUMX(Sales, (Sales[UnitPrice] - Sales[UnitCost]) * Sales[Qty])6. AVERAGEX
Calculates the average of an expression evaluated row-by-row over a table.
Category 3: Time Intelligence
Time intelligence functions allow you to compare metrics across different dates (e.g. Month-on-Month, Year-on-Year) easily.
7. SAMEPERIODLASTYEAR
SalesLY = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR('Calendar'[Date]))8. TOTALYTD
Calculates the year-to-date total of an expression.
YTDRevenue = TOTALYTD(SUM(Sales[Revenue]), 'Calendar'[Date])9. DATEADD
Shifts dates by a specified interval (Day, Month, Quarter, Year).
SalesPreviousMonth = CALCULATE(SUM(Sales[Revenue]), DATEADD('Calendar'[Date], -1, MONTH))Category 4: Logic & Safe Math
10. DIVIDE
Performs division with built-in safe handling of divide-by-zero errors, returning an alternate value (default is blank) instead of crashing.
ProfitMargin = DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0)11. RELATED
Retrieves a related value from another table using active relationships. Works on the "one" side of a one-to-many relationship.
CustomerState = RELATED(Customer[State])Master DAX and Power BI Dashboarding
Learning DAX requires structured practice. At Sasthra Analytics, lead trainer Mr. Anil Kumar (16+ years industry experience) guides you through all 25 essential DAX formulas, filter contexts, row contexts, variables, security configurations, and OneLake Fabric workspaces using live enterprise databases.
Enquire About the Program