BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- What's new in Analysis Services 2016 (tabular model)?
- Improvements to editing DAX in SSAS 2016 (Tabular Model)
- Changes to the DAX language in SSAS Tabular 2016 (this blog)
- Bi-directional / many-to-many relationships in Tabular 2016
- 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:
This function ranks products by their price, and is difficult to understand!
With variables, you can now rewrite this as follows:
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:
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:
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. |
- What's new in Analysis Services 2016 (tabular model)?
- Improvements to editing DAX in SSAS 2016 (Tabular Model)
- Changes to the DAX language in SSAS Tabular 2016 (this blog)
- Bi-directional / many-to-many relationships in Tabular 2016
- Other improvements to Analysis Services 2016 Tabular