Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

New DAX functions in SQL Server 2016


By:   |   Read Comments   |   Related Tips: > 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:

calendar results

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.

calendarauto results

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

calendar auto with parameters

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:

PI values

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:

trigonometric functions

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:

values

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:

con in degrees

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:

trigonometric functions

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:

median values

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


Last Update:





About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools