New DAX functions in SQL Server 2016

By: Daniel Calbimonte | Comments | Related: > Analysis Services Development

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:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips