Post

# 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

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)``

## 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.

Recommended Free Ebook
Similar Articles