Last Updated on: 21st April 2024, 08:25 am
DAX stands for Data Analysis Expressions. It’s a formula and query language used in Microsoft Power BI, Power Pivot, and Analysis Services Tabular projects. DAX is designed to work with tables and columns, much like Excel formulas, but it’s optimized for handling large volumes of data and performing complex calculations.
With DAX, you can create calculated columns, tables, and measures to perform various types of data analysis, such as aggregations, comparisons, and statistical calculations. It’s widely used in business intelligence and analytics to derive insights from data and create interactive visualizations.
51 Most Useful DAX (Data Analysis Expressions) Formulas
1. SUM
Description: Calculates the sum of values in a column or expression.
Example:
Total Sales = SUM(Sales[Amount])
Calculates the total sales amount from the ‘Sales’ table.
2. AVERAGE
Description: Calculates the average of values in a column or expression.
Example:
Average Sales = AVERAGE(Sales[Amount])
Calculates the average sales amount from the ‘Sales’ table.
3. COUNT
Description: Counts the number of rows in a table or the number of values in a column.
Example:
Total Customers = COUNT(Customers[CustomerID])
Counts the total number of customers in the ‘Customers’ table.
4. MAX
Description: Returns the maximum value from a column or expression.
Example:
Highest Sales = MAX(Sales[Amount])
Find the highest sales amount from the ‘Sales’ table.
5. MIN
Description: Returns the minimum value from a column or expression.
Example:
Lowest Sales = MIN(Sales[Amount])
Finds the lowest sales amount from the ‘Sales’ table.
6. DISTINCT
Description: Returns a one-column table containing unique values from a column or table expression.
Example:
Unique Customers = DISTINCT(Sales[CustomerID])
Returns a list of unique customer IDs from the ‘Sales’ table.
7. RELATED
Description: Returns a related value from another table.
Example:
Customer Country = RELATED(Customers[Country])
Retrieves the country associated with a customer from the ‘Customers’ table.
8. FILTER
Description: Returns a table that has been filtered based on specified criteria.
Example:
High Value Sales = FILTER(Sales, Sales[Amount] > 1000)
Filters the ‘Sales’ table to include only sales with amounts greater than 1000.
9. CALCULATE
Description: Evaluates an expression in a modified filter context.
Example:
Total Sales 2019 = CALCULATE(SUM(Sales[Amount]), 'Calendar'[Year] = 2019)
Calculates the total sales amount for the year 2019.
10. ALL
Description: Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
Example:
Total Sales (All) = CALCULATE(SUM(Sales[Amount]), ALL(Sales))
Calculates the total sales amount regardless of any filters applied to the ‘Sales’ table.
11. DISTINCTCOUNT
Description: Returns the count of distinct values in a column.
Example:
Unique Customers Count = DISTINCTCOUNT(Sales[CustomerID])
Counts the number of unique customers in the ‘Sales’ table.
12. CONCATENATEX
Description: Concatenates the result of an expression evaluated for each row in a table, using a specified delimiter.
Example:
Customer List = CONCATENATEX(Customers, Customers[Name], ", ")
Creates a comma-separated list of customer names from the ‘Customers’ table.
13. SUMX
Description: Iterates over each row in a table, evaluates an expression, and returns the sum of the results.
Example:
Total Sales (SumX) = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
Calculates the total sales amount by multiplying quantity with unit price for each row and summing the results.
14. AVERAGEX
Description: Iterates over each row in a table, evaluates an expression, and returns the average of the results.
Example:
Average Sales (AvgX) = AVERAGEX(Sales, Sales[Amount])
Calculates the average sales amount for each row in the ‘Sales’ table.
15. COUNTROWS
Description: Counts the number of rows in a table or table expression.
Example:
Total Orders = COUNTROWS(Orders)
Counts the total number of orders in the ‘Orders’ table.
16. DIVIDE
Description: Divides two numbers and handles errors gracefully by returning a specified alternate result.
Example:
Sales Conversion Rate = DIVIDE(COUNT(Sales[Converted]), COUNT(Sales[Visitors]), 0)
Calculates the conversion rate of visitors to converted sales, handling division by zero errors.
17. BLANK
Description: Returns a blank value.
Example:
Missing Sales = IF(ISBLANK([Total Sales]), "No sales data available", [Total Sales])
Displays a message if total sales data is missing.
18. DATE
Description: Creates a date from the specified year, month, and day.
Example:
Date = DATE(2022, 4, 15)
Creates a date for April 15, 2022.
19. CALCULATETABLE
Description: Evaluates an expression over a table, with the option to modify the filter context.
Example:
Top Customers = CALCULATETABLE('Customers', 'Customers'[Total Purchases] > 1000)Returns a table of customers with total purchases exceeding 1000.
20. DISTINCTCOUNTNOBLANK
Description: Returns the count of distinct non-blank values in a column.
Example:
Unique Products Sold = DISTINCTCOUNTNOBLANK(Sales[ProductID])
Counts the number of unique products sold, excluding any blank product IDs.
21. EARLIER
Description: Returns the value of a column from the previous row in the current context.
Example:
Sales Growth = Sales[Amount] - EARLIER(Sales[Amount])
Calculates the sales growth compared to the previous row.
22. RANKX
Description: Returns the rank of a value in a column, optionally based on a specified expression and order.
Example:
Sales Rank = RANKX(Sales, Sales[Amount])
Calculates the rank of sales amounts in the ‘Sales’ table.
23. PERCENTILEX.INC
Description: Returns the nth percentile of values in a column, inclusive of the nth percentile value.
Example:
90th Percentile Sales = PERCENTILEX.INC(Sales, Sales[Amount], 0.9)
Calculates the 90th percentile sales amount in the ‘Sales’ table.
24. SWITCH
Description: Evaluates a list of conditions and returns the result corresponding to the first true condition.
Example:
Sales Category = SWITCH(TRUE(), [Total Sales] > 10000, "High", [Total Sales] > 5000, "Medium", "Low")
Categorizes sales into “High”, “Medium”, or “Low” based on their total amount.
25. USERELATIONSHIP
Description: Specifies a relationship to be used in a calculation, overriding the active relationship.
Example:
Sales Amount (Alternate Date) = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], 'AlternateDate'[Date]))
Calculates the sales amount using an alternate date relationship.
26. LOOKUPVALUE
Description: Returns the value of a column from a single row that meets the specified criteria.
Example:
Customer Name = LOOKUPVALUE(Customers[Name], Customers[CustomerID], 123)
Returns the name of the customer with the ID 123.
27. RELATEDTABLE
Description: Returns a table related to the current table by a foreign key relationship.
Example:
Customer Orders = RELATEDTABLE(Orders)
Returns all orders related to the current customer.
28. REMOVEFILTERS
Description: Removes all filters from the specified columns or tables.
Example:
Total Sales (No Filters) = CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS())
Calculates the total sales amount without any filters applied.
29. SELECTCOLUMNS
Description: Returns a table with selected columns from another table.
Example:
Customer Names = SELECTCOLUMNS(Customers, "Name", Customers[Name])
Returns a table with only the “Name” column from the ‘Customers’ table.
30. ADDCOLUMNS
Description: Returns a table with additional calculated columns.
Example:
Customer Profitability = ADDCOLUMNS(Customers, "Profit", [Total Sales] - [Total Costs])
Adds a “Profit” column to the ‘Customers’ table calculated as total sales minus total costs.
31. COALESCE
Description: Returns the first non-blank value from a list of expressions.
Example:
Preferred Language = COALESCE(User[PreferredLanguage], "English")
Returns the preferred language of the user or defaults to English if not specified.
32. RELATEDTABLE
Description: Returns a table related to the current table by a foreign key relationship.
Example:
Product Sales = RELATEDTABLE(Sales)
Returns all sales related to the current product.
33. ISFILTERED
Description: Checks if a column or table has been filtered.
Example:
Is Date Filtered = ISFILTERED('Calendar'[Date])Checks if the date column has been filtered.
34. FIRSTDATE
Description: Returns the earliest date from a column containing dates.
Example:
First Order Date = FIRSTDATE(Orders[OrderDate])
Returns the date of the first order in the ‘Orders’ table.
35. LASTDATE
Description: Returns the latest date from a column containing dates.
Example:
Last Order Date = LASTDATE(Orders[OrderDate])
Returns the date of the last order in the ‘Orders’ table.
36. DATEADD
Description: Adds or subtracts a specified number of units to a date.
Example:
Next Month = DATEADD('Calendar'[Date], 1, MONTH)Returns the date one month after the specified date.
37. DATEDIFF
Description: Calculates the difference between two dates.
Example:
Days Between Orders = DATEDIFF(Orders[OrderDate], Orders[PreviousOrderDate], DAY)
Calculates the number of days between consecutive orders.
38. TOTALYTD
Description: Calculates a year-to-date total for a specified expression.
Example:
Total Sales YTD = TOTALYTD(SUM(Sales[Amount]), 'Calendar'[Date])
Calculates the year-to-date total sales amount.
39. TOTALMTD
Description: Calculates a month-to-date total for a specified expression.
Example:
Total Sales MTD = TOTALMTD(SUM(Sales[Amount]), 'Calendar'[Date])
Calculates the month-to-date total sales amount.
40. FIRSTNONBLANK
Description: Returns the first non-blank value from a column, optionally evaluating an expression.
Example:
First Sale Amount = FIRSTNONBLANK(Sales[Amount], 0)
Returns the first non-blank sales amount or 0 if no value is found.
41. LASTNONBLANK
Description: Returns the last non-blank value from a column, optionally evaluating an expression.
Example:
Last Sale Amount = LASTNONBLANK(Sales[Amount], 0)
Returns the last non-blank sales amount or 0 if no value is found.
42. SELECTEDVALUE
Description: Returns the single value in a column when there’s only one value, otherwise returns a default value.
Example:
Selected Product = SELECTEDVALUE(Products[ProductID], "Multiple Products")
Returns the selected product ID or “Multiple Products” if more than one product is selected.
43. USERNAME
Description: Returns the username of the current user.
Example:
Current User = USERNAME()
Returns the username of the current user.
44. USERPRINCIPALNAME
Description: Returns the user principal name (UPN) of the current user.
Example:
Current User UPN = USERPRINCIPALNAME()
Returns the user principal name (email address) of the current user.
45. USERNAME
Description: Returns the username of the current user.
Example:
Current User = USERNAME()
Returns the username of the current user.
46. EARLIEST
Description: Returns the earliest date among a set of dates.
Example:
Earliest Date = EARLIEST('Calendar'[Date1], 'Calendar'[Date2], 'Calendar'[Date3])Returns the earliest date among Date1, Date2, and Date3.
47. LATEST
Description: Returns the latest date among a set of dates.
Example:
Latest Date = LATEST('Calendar'[Date1], 'Calendar'[Date2], 'Calendar'[Date3])Returns the latest date among Date1, Date2, and Date3.
48. CONTAINS
Description: Checks if a table or column contains a specific value.
Example:
Has High Sales = CONTAINS(Sales, Sales[Amount], 1000)
Checks if the ‘Sales’ table contains any sales with an amount of 1000.
49. PATHCONTAINS
Description: Checks if a path contains a specific value.
Example:
Has Path = PATHCONTAINS('Paths', "PathName", "Value")Checks if the ‘Paths’ table contains the specified value in the “PathName” column.
50. PATHLENGTH
Description: Returns the length of a path.
Example:
Path Length = PATHLENGTH('Paths', "PathName")Returns the length of the path specified in the “PathName” column of the ‘Paths’ table.
51. PATHITEM
Description: Returns the nth item in a path.
Example:
Path Item = PATHITEM('Paths', "PathName", 2)Returns the second item in the path specified in the “PathName” column of the ‘Paths’ table.
These DAX formulas cover various functionalities commonly used in Power BI and other analysis tools for manipulating and analyzing data.
