DAX (Data Analysis Expressions) is the formula language behind every Power BI measure and calculated column. Unlike Excel formulas that work cell by cell, DAX operates on entire tables and columns. Here are the ten DAX functions that cover the vast majority of real-world reporting needs.
1. SUM and SUMX
SUM is straightforward: SUM(Sales[Amount]) adds up a column. But SUMX is an iterator that evaluates an expression for each row before summing. Example: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) calculates line-item totals and sums them. Use SUM when you have a single column to add. Use SUMX when you need a row-by-row calculation first.
2. CALCULATE
CALCULATE is the single most important DAX function. It evaluates an expression in a modified filter context. Example: CALCULATE(SUM(Sales[Amount]), Products[Category] = "Electronics") gives you sales for Electronics only, regardless of what slicers are active on the page. CALCULATE is how you override and control filters in DAX.
3. FILTER
FILTER returns a table that satisfies a condition. It is often used inside CALCULATE: CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Discount] > 0.1)) sums only sales where the discount exceeds 10%. FILTER is useful when your condition involves a calculation rather than a simple column value.
4. ALL and ALLEXCEPT
ALL removes filters from a table or column. The most common pattern is calculating a percentage of total: DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales))) gives each row's share of the grand total. ALLEXCEPT removes all filters except specified columns, useful for subtotals.
5. DIVIDE
Use DIVIDE instead of the / operator to avoid division-by-zero errors. DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0) returns 0 when revenue is zero instead of throwing an error. It is cleaner than wrapping every division in an IF statement.
6. DISTINCTCOUNT
DISTINCTCOUNT counts unique values in a column. DISTINCTCOUNT(Sales[CustomerID]) tells you how many unique customers made a purchase. This is one of the most common KPI calculations for any dashboard: unique customers, unique products sold, or unique visitors.
7. SELECTEDVALUE
SELECTEDVALUE returns the value in a column when exactly one value is visible in the current filter context. It is perfect for dynamic titles and conditional measures: "Sales for " & SELECTEDVALUE(Geography[Region], "All Regions") gives you a dynamic report title that changes as users filter.
8. DATESINPERIOD and time intelligence
Power BI has a family of time intelligence functions. TOTALYTD(SUM(Sales[Amount]), Dates[Date]) gives year-to-date totals. SAMEPERIODLASTYEAR returns the same period from last year for YoY comparisons. DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -3, MONTH) gives the last three months of data. These require a proper date table in your model.
9. RANKX
RANKX ranks values across a table. RANKX(ALL(Products), SUM(Sales[Amount])) ranks each product by total sales. Add it to a table visual and you instantly see your top performers. Use the fourth argument to control rank order (ASC or DESC) and the fifth for how ties are handled.
10. SWITCH
SWITCH replaces nested IF statements. SWITCH(TRUE(), Sales[Amount] > 10000, "High", Sales[Amount] > 5000, "Medium", "Low") categorizes sales into tiers. SWITCH(TRUE(), ...) is the most readable pattern for multiple conditions. It keeps your measures clean and maintainable.
Learning more
These ten functions cover most reporting scenarios, but DAX has much more depth. Time intelligence, iterator functions, and calculation groups unlock advanced analytics. Our [Power BI training](/courses/power-bi/) course covers all of these with hands-on exercises using real business data.