DAX functions in Power BI enable users to perform advanced calculations and visualization by creating measures and calculated columns. Below are the top 20 most commonly used DAX functions, along with practical examples.
1. SUM
Purpose: Adds up all values in a column.
Syntax: SUM(column)
Example:TotalSales = SUM(Sales[UnitPrice] * Sales[Quantity])
Calculates the total sales amount by multiplying unit price and quantity, then summing the result.
2. AVERAGE
Purpose: Computes the arithmetic mean of a column.
Syntax: AVERAGE(column)
Example:AvgQuantity = AVERAGE(Sales[Quantity])
Returns the average quantity sold across all transactions.
3. MIN
Purpose: Finds the smallest value in a column.
Syntax: MIN(column)
Example:MinPrice = MIN(Sales[UnitPrice])
Returns the lowest unit price in the Sales table.
4. MAX
Purpose: Finds the largest value in a column.
Syntax: MAX(column)
Example:MaxPrice = MAX(Sales[UnitPrice])
Returns the highest unit price in the Sales table.
5. COUNT
Purpose: Counts non-blank values in a column.
Syntax: COUNT(column)
Example:TotalOrders = COUNT(Sales[OrderID])
Counts the number of orders in the Sales table.
6. DISTINCTCOUNT
Purpose: Counts unique values in a column.
Syntax: DISTINCTCOUNT(column)
Example:UniqueProducts = DISTINCTCOUNT(Sales[Product])
Returns the number of unique products sold.
7. CALCULATE
Purpose: Modifies the context of a calculation with filters.
Syntax: CALCULATE(expression, filter1, filter2, ...)
Example:SalesWest = CALCULATE(SUM(Sales[UnitPrice] * Sales[Quantity]), Sales[Region] = "West")
Calculates total sales for the “West” region only.
8. FILTER
Purpose: Returns a table filtered based on a condition.
Syntax: FILTER(table, condition)
Example:HighSales = CALCULATE(SUM(Sales[UnitPrice]), FILTER(Sales, Sales[Quantity] > 10))
Sums the unit price for transactions where quantity exceeds 10.
9. ALL
Purpose: Removes filters from a table or column.
Syntax: ALL(table | column)
Example:TotalSalesAll = CALCULATE(SUM(Sales[UnitPrice] * Sales[Quantity]), ALL(Sales))
Calculates total sales, ignoring any applied filters.
10. DIVIDE
Purpose: Safely performs division, handling divide-by-zero errors.
Syntax: DIVIDE(numerator, denominator, [alternate result])
Example:ProfitMargin = DIVIDE(SUM(Sales[Profit]), SUM(Sales[UnitPrice]), 0)
Calculates profit margin, returning 0 if the denominator is zero.
11. IF
Purpose: Returns one value if a condition is true, another if false.
Syntax: IF(condition, valueIfTrue, valueIfFalse)
Example:SalesStatus = IF(SUM(Sales[Quantity]) > 100, "High", "Low")
Labels sales as “High” if quantity exceeds 100, otherwise “Low”.
12. SWITCH
Purpose: Evaluates an expression and returns a value based on multiple conditions.
Syntax: SWITCH(expression, value1, result1, [value2, result2], ..., [default])
Example:RegionCategory = SWITCH(Sales[Region], "East", "A", "West", "B", "C")
Assigns categories (A, B, C) based on the region.
13. DATEADD
Purpose: Shifts dates by a specified interval.
Syntax: DATEADD(date_column, number, interval)
Example:SalesLastYear = CALCULATE(SUM(Sales[UnitPrice] * Sales[Quantity]), DATEADD(Sales[Date], -1, YEAR))
Calculates total sales for the previous year.
14. TOTALYTD
Purpose: Calculates a year-to-date total.
Syntax: TOTALYTD(expression, date_column)
Example:YTDSales = TOTALYTD(SUM(Sales[UnitPrice] * Sales[Quantity]), Sales[Date])
Returns the year-to-date sales total.
15. SAMEPERIODLASTYEAR
Purpose: Returns values from the same period in the previous year.
Syntax: SAMEPERIODLASTYEAR(date_column)
Example:LastYearSales = CALCULATE(SUM(Sales[UnitPrice] * Sales[Quantity]), SAMEPERIODLASTYEAR(Sales[Date]))
Sums sales from the same period last year.
16. RELATED
Purpose: Fetches a value from a related table.
Syntax: RELATED(column)
Example:ProductCategory = RELATED(Products[Category])
Pulls the product category from a related “Products” table into the Sales table.
17. CONCATENATE
Purpose: Combines two text strings into one.
Syntax: CONCATENATE(text1, text2)
Example:ProductRegion = CONCATENATE(Sales[Product], Sales[Region])
Creates a combined string like “LaptopWest”.
18. RANKX
Purpose: Ranks items in a table based on an expression.
Syntax: RANKX(table, expression, [value], [order])
Example:SalesRank = RANKX(ALL(Sales[Product]), SUM(Sales[UnitPrice] * Sales[Quantity]))
Ranks products by total sales.
19. BLANK
Purpose: Returns a blank value.
Syntax: BLANK()
Example:AdjustedProfit = IF(SUM(Sales[Profit]) < 0, BLANK(), SUM(Sales[Profit]))
Returns blank if profit is negative, otherwise shows profit.
20. EARLIER
Purpose: Refers to an earlier row context in nested calculations.
Syntax: EARLIER(column)
Example:RankWithinRegion = CALCULATE(COUNTROWS(Sales), FILTER(Sales, Sales[Region] = EARLIER(Sales[Region]) && Sales[UnitPrice] > EARLIER(Sales[UnitPrice]))) + 1
Ranks rows within the same region by unit price.
Conclusion
These 20 DAX functions form the backbone of data analysis in Power BI, enabling everything from basic aggregations to complex time-intelligence and conditional logic. By mastering these functions, you can unlock deeper insights and create more dynamic reports. Experiment with these examples using your own dataset to see their full potential!
