Top 20 DAX Functions: Explanation and Examples for Power BI

1. SUM()

• Description: Adds up all values in a column.
• Example: SUM(Sales[Amount])

2. AVERAGE()

• Description: Returns the average of values in a column.
• Example: AVERAGE(Sales[Amount])

3. COUNT()

• Description: Counts the number of rows in a column.
• Example: COUNT(Sales[OrderID])

4. DISTINCTCOUNT()

• Description: Returns the count of unique values in a column.
• Example: DISTINCTCOUNT(Sales[CustomerID])

5. CALCULATE()

• Description: Evaluates an expression in a modified context.
• Example: CALCULATE(SUM(Sales[Amount]), Sales[Region] = "West")

6. FILTER()

• Description: Returns a table that meets a specific condition.
• Example: FILTER(Sales, Sales[Amount] > 1000)

7. ALL()

• Description: Ignores all filters on the specified column or table.
• Example: ALL(Sales[Region])

8. RELATED()

• Description: Returns a value from a related table.
• Example: RELATED(Customers[CustomerName])

9. RELATEDTABLE()

• Description: Returns a table related to the current row context.
• Example: RELATEDTABLE(Sales)

10. LOOKUPVALUE()

• Description: Returns a value based on a condition.
• Example: LOOKUPVALUE(Sales[Amount], Sales[OrderID], 101)

11. IF()

• Description: Returns different values based on a condition.
• Example: IF(Sales[Amount] > 1000, "High", "Low")

12. SWITCH()

• Description: Returns different results based on multiple conditions.
• Example: SWITCH(Sales[Region], "West", 1, "East", 2, 0)

13. RANKX()

• Description: Returns the rank of a value in a table.
• Example: RANKX(ALL(Sales), Sales[Amount])

14. DIVIDE()

• Description: Performs division with error handling.
• Example: DIVIDE(Sales[Amount], Sales[Quantity])

15. CONCATENATE()

• Description: Joins two strings into one.
• Example: CONCATENATE(Sales[FirstName], Sales[LastName])

16. CONCATENATEX()

• Description: Concatenates values from a table.
• Example: CONCATENATEX(Sales, Sales[ProductName], ", ")

17. EARLIER()

• Description: Refers to an earlier row context.
• Example: CALCULATE(SUM(Sales[Amount]), EARLIER(Sales[Region]))

18. VALUES()

• Description: Returns a unique list of values from a column.
• Example: VALUES(Sales[Region])

19. TIME()

• Description: Creates a time value from hour, minute, and second.
• Example: TIME(10, 30, 0)

20. FORMAT()

• Description: Changes the format of a value.
• Example: FORMAT(Sales[Amount], "Currency")