Introduction
This is the fourth part of the DAX Functions series. You can also visit our other articles in the same series. i.e.
DAX Statistical Functions
AVERAGE function in DAX
Returns the mean average of values.
Syntax
AVERAGE(column)
Example
AVERAGE(Sales[Revenue])
Returns the average revenue in the Sales table.
MEDIAN function in DAX
Returns the median value in a set.
Syntax
MEDIAN(column)
Example
MEDIAN(Sales[Profit])
Returns the median value of profit in the Sales table.
PERCENTILE function in DAX
Returns Nth percentile value from a set.
Syntax
PERCENTILE(column, N)
Example
PERCENTILE(Sales[Revenue], 95)
Returns the 95th percentile revenue value in the Sales table.
STDEV function in DAX
Estimates standard deviation based on sample set.
Syntax
STDEV(column)
Example
STDEV(Sales[Revenue])
Returns estimated standard deviation of revenue in Sales table sample.
VAR function in DAX
Estimates variance based on sample set
Syntax
VAR(column)
Example
VAR(Sales[Profit])
Returns estimated variance of profit values in the Sales table sample.
DAX Table manipulation functions
ADDCOLUMNS function in DAX
Returns a table with added columns.
Syntax
ADDCOLUMNS(table, column1, expression1, ...)
Example
ADDCOLUMNS(Sales, "Profit Ratio", [Profit]/[Revenue])
Adds a Profit Ratio column calculated from Profit and Revenue.
SUMMARIZE function in DAX
Returns a summary table grouped by specific columns.
Syntax
SUMMARIZE(table, group1, group2, aggregate1, aggregate2, ...)
Example
SUMMARIZE(Sales, Sales[Country], "Revenue", SUM(Sales[Revenue]) )
Returns a summary table with Revenue totaled by Country.
GROUPBY function in DAX
Splits table into groups based on by_column.
Syntax
GROUPBY(table, by_column, new_column, expression)
Example
GROUPBY(Sales, Sales[Customer], "Max Sale", MAX(Sales[Revenue]))
Groups Sales table by Customer and returns their max sale.
INTERSECT function in DAX
Returns the intersection of two tables, keeping only common rows.
Syntax
INTERSECT(table1, table2)
Example
INTERSECT(Table1, Table2)
Keeps only rows present in both Table1 and Table2.
EXCEPT function in DAX
Returns rows from the first table that are not present in the second table.
Syntax
EXCEPT(table1, table2)
Example
EXCEPT(Table1, Table2)
Keeps only rows from Table1 that are not found in Table2.
DAX Text functions
UPPER function in DAX
Converts text to upper case.
Syntax
UPPER(text)
Example
UPPER(Sales[Product])
Converts Product names to upper case.
LOWER function in DAX
Converts text to lower case.
Syntax
LOWER(text)
Example
LOWER(Sales[Customer])
Converts Customer names to lower case.
PROPER function in DAX
Capitalize the first letter of each word in the text.
Syntax
PROPER(text)
Example
PROPER(Sales[Product])
Capitalize the first letter of each word in Product names.
LEN function in DAX
Returns the number of characters in text.
Syntax
LEN(text)
Example
LEN(Sales[Product])
Returns the length of each Product name.
CONCATENATE function in DAX
Joins two or more text strings.
Syntax
CONCATENATE(text1, text2, ...)
Example
CONCATENATE(Sales[Customer], " ", Sales[Product])
Joins Customer and Product with a colon separator.
DAX Time intelligence functions
SAMEPERIODLASTYEAR function in DAX
Returns rows with dates from the same period last year as filter context.
Syntax
SAMEPERIODLASTYEAR(Dates[Date])
Example
CALCULATE([Revenue], 7
SAMEPERIODLASTYEAR(Dates[Date]))
Returns Revenue for current filter context dates last year.
DATESYTD function in DAX
Returns rows with dates within the current year-to-date filter context.
Syntax
DATESYTD(Dates[Date])
Example
CALCULATE([Revenue],
DATESYTD(Dates[Date]))
Returns Revenue for dates within the current year to date.
DATESMTD function in DAX
Returns rows with dates within a current month-to-date filter context.
Syntax
DATESMTD(Dates[Date])
Example
CALCULATE([Revenue],
DATESMTD(Dates[Date]))
Returns Revenue for dates within a current month to date.
DATESQTD function in DAX
Returns rows with dates within the current quarter-to-date filter context.
Syntax
DATESQTD(Dates[Date])
Example
CALCULATE([Revenue],
DATESQTD(Dates[Date]))
Returns Revenue for dates within a current quarter to date.
DATESINYEAR function in DAX
Returns rows with dates within the year of the filter context.
Syntax
DATESINYEAR(Dates[Date])
Example
CALCULATE([Revenue],
DATESINYEAR(Dates[Date]))
Returns Revenue for dates within the current year.