This blog summarises the (many) new features in Analysis Services 2016 (Tabular)
Part three of a five-part series of blogs

Microsoft have made lots of improvements to SSAS (Tabular model) - we particularly like the new DAX editor! This blog lists all of the most interesting changes, divided into 4 separate sections.

  1. What's new in Analysis Services 2016 (tabular model)?
  2. Improvements to editing DAX in SSAS 2016 (Tabular Model)
  3. Changes to the DAX language in SSAS Tabular 2016 (this blog)
  4. Bi-directional / many-to-many relationships in Tabular 2016
  5. Other improvements to Analysis Services 2016 Tabular

This is part of a wide-ranging blog explaining the new features in every part of SQL Server 2016.

Posted by Andy Brown on 08 July 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Changes to the DAX  language in SSAS Tabular 2016

This page shows improvements to the language used to calculate measures in Analysis Services 2016 (Tabular Model).

Many of these improvements will only be available to you if you use SSAS 2016 as a workspace server.

DAX variables example 1 - avoiding the EARLIER function

If you spend your life writing DAX expressions, then said life is about to get easier.  Here's one example of how you could use a variable to bypass the dreaded EARLIER function:

EARLIER function example

This function ranks products by their price, and is difficult to understand!

With variables, you can now rewrite this as follows:

Avoiding EARLIER

Create a variable to hold the full price of each product outside the filter context; you can then refer to this inside the DAX expression.

 

Anything which means you don't have to use the EARLIER function has to be good news!

DAX variables example 2 - holding tables in a variable

Remarkably, DAX variables can also hold tables.  Consider this simple date measure:

Last year's sales

We're comparing this period's sales with the same period for the previous year.

 

In versions of SSAS Tabular before 2016 you could use a measure like this:

Last year sales:=CALCULATE(

SUM('Transaction'[Quantity]),

SAMEPERIODLASTYEAR('Calendar'[Date])

)

From 2016 onwards you could optionally use a variable to hold the dates for the current query context:

Variable holding dates

This would give exactly the same results.

All very clever!

New DAX functions

SSAS Tabular 2016 contains over 50 new DAX functions, as listed here.  Here's a sample to whet your appetite (go to the link for a full listing):

Function What it does
DATEDIFF Calculate the number of time periods between two dates.
CALENDAR Returns a single-column table of dates in any specified range.
PRODUCT Returns the product of a set of numbers in a column.
GEOMEAN  Returns the geometric mean of the numbers in a column.
PERCENTILE.EXC  Returns a given percentile of a set of data.
XIRR / XNPV Returns the internal rate of return or net present value of a set of data.
MEDIAN Returns the median of the numbers in a column.

 

 

  1. What's new in Analysis Services 2016 (tabular model)?
  2. Improvements to editing DAX in SSAS 2016 (Tabular Model)
  3. Changes to the DAX language in SSAS Tabular 2016 (this blog)
  4. Bi-directional / many-to-many relationships in Tabular 2016
  5. Other improvements to Analysis Services 2016 Tabular
This blog has 0 threads Add post