# New DAX functions in SQL Server 2016

##### Problem

There are new features for Data Analysis Expressions (DAX) in SQL Server 2016 and in this tip we will look at a few of these new DAX functions.

##### Solution

SQL Server 2016 comes with several new features including new DAX functions to enrich the functionality. In this tip, we will show some of these new DAX functions.

## Requirements

• SQL Server 2016 installed with a Tabular Server installed.
• The Adventureworks Tabular solution installed.
• I assume that you already have experience in DAX. If not, I recommend you to read this tip first, introduction to DAX.

In order to start, open SSMS and connect to a Tabular Server.

## DAX Calendar Function

Let's start with the CALENDAR function. This new function returns a column with the consecutive dates from a start date until a specified end date. The following example will show all the days between January 1, 2015 and January 31, 2015.

`evaluate(CALENDAR (DATE (2015, 1, 1), DATE (2015, 1, 31)))`

The result displayed by the DAX query is the following: ## DAX CALENDARAUTO Function

Another new statement is CALENDARAUTO. This statement returns a set of dates in a column based on the data in the model.

`evaluate(CALENDARAUTO ())`

The statement will return the dates of the Adventureworks tabular model starting on January 1, 1916 until December 31, 2014. You can specify a parameter in the CALENDARAUTO with values from 1 to 12:

`evaluate(CALENDARAUTO (6))`

If you specify a value of 6, you will get a set of consecutive dates starting on July 1, 1915 (6 months added to the earliest date) to June 30, 2015 (6 months added to the maximum date). ## DAX PI Function

Another new function is PI, which is the mathematical constant that represents the ratio of a circumference in relation to its diameter. This value is approximately: 3.14159. Let's call this function using DAX:

`evaluate(	ROW 	(		"PI",PI() 	))`

The result returned would be the following: ## DAX SIN, COS and TAN Functions

We also have new trigonometric functions like sin, cos and tan. In a right triangle, there are fixed relationships between the Hypotenuse, Opposite and the Adjacent: The formulas to calculate these values are as follows (if you do not remember your school classes):

Sin A = a/c

Cos A = b/c

Tan A = a/b

Let's use this function to calculate the cos of 45 radians:

`evaluate(	ROW 	(		"COS",COS(45)	))`

The result will be the following: As you can see, the trigonometric functions are in radians. If you need to work in degrees there are 2 options:

Option 1 is to convert the degrees to radians by multiplying the radians by PI/180:

`evaluate(	ROW 	(		"COS IN DEGREES, METHOD 1",COS(45*PI()/180)	))`

The result in degrees is the following: The other method is to convert the radians to degrees using the RADIANS function (which is also new in SQL Server 2016):

`evaluate(	ROW 	(		"COS IN DEGREES METHOD 2",COS(RADIANS(45))	))`

You can for example calculate sin^2(x) + cos^2(x)1:

`evaluate(	ROW 	(		"sin^2+cos^2",(power(sin(45),2)+power(cos(45),2))	))`

The result of the formula is 1: ## DAX MEDIAN Function

There are other functions like the MEDIAN, which is the middle value of a list of values or the mean of two middle values if there is no single middle value. In DAX, we can use this example:

`evaluate(         summarize(                   'Internet Sales',                   [ProductKey],                   "Total Sales", MEDIAN('Internet Sales'[Sales Amount] )                ))`

The example shows the MEDIAN of the internet sales group by ProductKey: ## Conclusions

In this tip, we covered some of the new functions incorporated in DAX for SQL Server 2016. We displayed some samples of the new functions. I hope you enjoyed this tip.

##### Next Steps
• There are several new functions in DAX, for a complete review this link.
• Check out these tips on DAX:

Last Updated: 2015-12-28 Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources

*Name
*Email

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.