Complete Guide to DAX Functions in Power BI

Data Analysis Expressions (DAX) is a powerful language in Power BI, designed to perform complex calculations, filter data, and create dynamic reports. Whether you’re new to DAX or looking to expand your knowledge, this guide provides 50 essential DAX functions, complete with examples to showcase how each function can be applied in real-world scenarios. We’ll also provide a detailed table at the end summarizing all the functions and their use cases.

1. SUM

The SUM function adds up all values in a column. It is commonly used for total calculations.

TotalSales = SUM('Sales'[SalesAmount])

2. AVERAGE

The AVERAGE function calculates the mean of a column of numbers.

AverageSales = AVERAGE('Sales'[SalesAmount])

3. COUNT

COUNT counts the number of non-blank values in a column.

TotalOrders = COUNT('Sales'[OrderID])

4. COUNTROWS

COUNTROWS counts the number of rows in a table.

RowCount = COUNTROWS('Sales')

5. DISTINCTCOUNT

The DISTINCTCOUNT function counts unique values in a column.

UniqueCustomers = DISTINCTCOUNT('Sales'[CustomerID])

6. MAX

The MAX function returns the largest value in a column.

MaxSales = MAX('Sales'[SalesAmount])

7. MIN

The MIN function returns the smallest value in a column.

MinSales = MIN('Sales'[SalesAmount])

8. CALCULATE

CALCULATE changes the context of a calculation by applying filters.

FilteredSales = CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[Region] = "North America")

9. ALL

The ALL function removes filters from a column or table.

TotalSalesAllRegions = CALCULATE(SUM('Sales'[SalesAmount]), ALL('Sales'[Region]))

10. ALLEXCEPT

The ALLEXCEPT function removes all filters except for the specified columns.

TotalSalesWithExceptions = CALCULATE(SUM('Sales'[SalesAmount]), ALLEXCEPT('Sales', 'Sales'[Product]))

11. DIVIDE

DIVIDE safely divides two numbers and returns zero when division by zero occurs.

ProfitMargin = DIVIDE([TotalProfit], [TotalRevenue], 0)

12. RELATED

RELATED retrieves a related value from another table.

ProductCategory = RELATED('Products'[Category])

13. RELATEDTABLE

RELATEDTABLE returns a table with all related rows from another table.

OrdersByCustomer = RELATEDTABLE('Orders')

14. SUMX

SUMX performs row-by-row calculations on a table and then sums the results.

TotalProfit = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[ProfitPerUnit])

15. AVERAGEX

AVERAGEX performs row-by-row calculations and then averages the results.

AverageProfit = AVERAGEX('Sales', 'Sales'[Quantity] * 'Sales'[ProfitPerUnit])

16. FILTER

FILTER returns a table filtered based on an expression.

HighValueSales = FILTER('Sales', 'Sales'[SalesAmount] > 10000)

17. EARLIER

EARLIER allows access to the value from an earlier row context in nested row operations.

RankSales = RANKX('Sales', 'Sales'[SalesAmount], , ASC, EARLIER('Sales'[Product]))

18. RANKX

RANKX returns the ranking of a value in a table.

RankSales = RANKX(ALL('Sales'), 'Sales'[SalesAmount])

19. ISBLANK

ISBLANK checks whether a value is blank and returns TRUE or FALSE.

BlankCheck = ISBLANK('Sales'[CustomerID])

20. IF

IF performs a conditional test and returns one value if true and another if false.

SalesCategory = IF('Sales'[SalesAmount] > 5000, "High", "Low")

21. SWITCH

SWITCH evaluates an expression against a list of values and returns corresponding results.

SalesRegion = SWITCH('Sales'[Region], "North", "North Region", "South", "South Region", "Other")

22. AND

AND checks whether two conditions are met.

IsHighValue = AND('Sales'[SalesAmount] > 10000, 'Sales'[Region] = "North America")

23. OR

OR checks whether at least one condition is met.

IsHighValueOrNorth = OR('Sales'[SalesAmount] > 10000, 'Sales'[Region] = "North America")

24. NOT

NOT reverses a logical value.

NotHighValue = NOT('Sales'[SalesAmount] > 10000)

25. CONTAINS

CONTAINS checks whether a table contains a specific value.

ContainsHighValue = CONTAINS('Sales', 'Sales'[SalesAmount], 10000)

26. VALUE

VALUE converts a text string into a number.

ConvertedValue = VALUE('Sales'[SalesAmountText])

27. FORMAT

FORMAT changes the format of a value to text.

FormattedSales = FORMAT('Sales'[SalesAmount], "Currency")

28. CONCATENATE

CONCATENATE joins two text strings into one.

FullName = CONCATENATE('Customer'[FirstName], 'Customer'[LastName])

29. LEFT

LEFT returns the leftmost characters from a text string.

LeftThreeChars = LEFT('Customer'[CustomerName], 3)

30. RIGHT

RIGHT returns the rightmost characters from a text string.

RightThreeChars = RIGHT('Customer'[CustomerName], 3)

31. MID

MID returns a substring from a text string.

MiddleName = MID('Customer'[CustomerName], 2, 5)

32. SEARCH

SEARCH finds the position of one text string within another.

PositionOfChar = SEARCH("A", 'Customer'[CustomerName])

33. SUBSTITUTE

SUBSTITUTE replaces existing text with new text.

ReplaceSpaceWithDash = SUBSTITUTE('Customer'[CustomerName], " ", "-")

34. LEN

LEN returns the length of a text string.

NameLength = LEN('Customer'[CustomerName])

35. NOW

NOW returns the current date and time.

CurrentDateTime = NOW()

36. TODAY

TODAY returns the current date.

CurrentDate = TODAY()

37. YEAR

YEAR returns the year from a date value.

YearOfSale = YEAR('Sales'[OrderDate])

38. MONTH

MONTH returns the month from a date value.

MonthOfSale = MONTH('Sales'[OrderDate])

39. DAY

DAY returns the day of the month from a date value.

DayOfSale = DAY('Sales'[OrderDate])

40. DATEDIFF

DATEDIFF calculates the difference between two dates.

DaysBetween = DATEDIFF('Sales'[StartDate], 'Sales'[EndDate], DAY)

41. EOMONTH

EOMONTH returns the last day of the month for a given date.

EndOfMonth = EOMONTH('Sales'[OrderDate], 0)

42. WEEKDAY

WEEKDAY returns the day of the week as a number.

WeekdayOfSale = WEEKDAY('Sales'[OrderDate])

43. WEEKNUM

WEEKNUM returns the week number of a date.

WeekOfSale = WEEKNUM('Sales'[OrderDate])

44. DATEADD

DATEADD shifts dates forward or backward.

SalesLastYear = CALCULATE(SUM('Sales'[SalesAmount]), DATEADD('Sales'[OrderDate], -1, YEAR))

45. CALENDAR

CALENDAR creates a continuous date range between two dates.

CalendarTable = CALENDAR(DATE(2020, 1, 1), DATE(2021, 12, 

31))

46. ADDCOLUMNS

ADDCOLUMNS adds calculated columns to a table.

SalesWithMargin = ADDCOLUMNS('Sales', "ProfitMargin", [Profit] / [SalesAmount])

47. SELECTCOLUMNS

SELECTCOLUMNS returns a table with selected columns.

SelectedSalesData = SELECTCOLUMNS('Sales', "OrderID", 'Sales'[OrderID], "SalesAmount", 'Sales'[SalesAmount])

48. UNION

UNION combines two or more tables with identical columns.

CombinedSalesData = UNION('Sales2020', 'Sales2021')

49. INTERSECT

INTERSECT returns the rows common to two tables.

CommonSalesData = INTERSECT('Sales2020', 'Sales2021')

50. EXCEPT

EXCEPT returns the difference between two tables.

SalesDifference = EXCEPT('Sales2020', 'Sales2021')

This article covered 50 DAX functions to help you master complex data analysis in Power BI. Be sure to explore the provided examples and practice building your own measures and calculated columns using these functions.

Below is a comprehensive table summarizing at least 50 DAX functions from the article, complete with their descriptions and examples:

Conclusion

This table provides a comprehensive summary of the 50 most commonly used DAX functions in Power BI. Each function plays a key role in transforming, aggregating, and analyzing your data. From basic calculations like SUM and AVERAGE to more advanced filtering and row context management using CALCULATE and FILTER, mastering these DAX functions will significantly enhance your data modeling skills.

For more details and a complete list of DAX functions, please refer to the official Microsoft DAX documentation.