Exploring Statistical, Table - manipulation and Text Functions in DAX

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.