Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enhancements for the DAX language in Analysis Services 2016


By:   |   Read Comments (1)   |   Related Tips: > Analysis Services Development

Attend these FREE MSSQLTips webcasts >> click to register


Problem

With the release of SQL Server 2016, several improvements were shipped for Analysis Services Tabular. A couple of key enhancements were made to the DAX language; the scripting and querying language for Tabular models. In this tip, we’ll give an overview of these new features. References will be given if previous tips have already been written about the subject.

Solution

All the mentioned improvements in this tip apply to Analysis Services Tabular 2016 or later, to Azure Analysis Services and to Power BI Desktop as well.

Improved DAX formula writing

When creating a Tabular model, you can write DAX formulas to create calculated columns or measures. Efforts have been made to improve the usability experience of writing DAX. This has been done by adding highlighting and the ability to add comments.

DAX writing - highlights and comments

You can also format your code by adding new lines or by indenting it.

DAX writing - formatting

There is also Intellisense included:

DAX writing - intellisense 1

This also works for referencing tables or columns:

DAX writing - intellisense 2

To help you out even more, there are error “squiggles” to point out mistakes in your DAX formula:

DAX writing - errors

You can find the actual error message in the Error List window. Another nice addition is the ability to save incomplete (or incorrect) measures. This might be useful when you’re in the middle of a formula and for some reason you need to stop writing and work on something else (for example creating another measure or column first so you can use it in your formula). The result will be marked as #Error for a measure:

DAX writing - incomplete measure

The same is true for calculated columns:

unit price

DAX variables

A new feature is the ability to use variables inside a DAX formula. Using variables might make your DAX code easier to write, easier to read and performance might improve as well. Variables can store the result of any DAX expression. Let’s illustrate with an example. I’m using the same Tabular model I created in the tip Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables – Part 3. We’re going to create a measure displaying the percentage of the grand total of the sales amount. In previous versions of SSAS, we had to use the following formula:

grand total dax old

With variables, we can split out the formula and calculate the grand total in advance (which might improve performance since you need to calculate it only once):

% of grand total dax variables

Although the entire formula is now longer, it is easier to read. Keep in mind you can also store tables in variables, besides scalar values.

If you want more information and examples of DAX variables:

New DAX functions

Lots of new functions have been added to the DAX language. They are too many (over 50!) to mention them all here, but you can find a full list at New DAX Functions.

Noteworthy new functions are CALENDAR, MEDIAN and CONCATENATEX, but there are many more especially in the statistical and mathematical area.

With the CALENDAR function, you can easily create a continuous range of dates. This can be the starting point of a date dimension. An example is given in the tip Scenarios for Using Calculated Tables in SSAS Tabular 2016 or Power BI Desktop – Part 2.

In previous versions it was quite difficult to calculate a median value. You had to sort the data set according to a measure and find the middle value. This led to complex and long formulas using a combination of CALCULATE, FILTER and EARLIER. You can find the formula for this statistical pattern at the DAX Patterns website. With the introduction of MEDIAN, this becomes a lot easier. You just need the following formula:

dax median

The result:

median dax pivot

Remark: it can take some time to find a good example using the Wide World Importers database. The data is randomly generated but apparently distributed around the median 10 for a lot of dimension attributes.

With CONCATENATEX, you can concatenate different columns together over the various rows. For example, we can create a formula this displays all sales territories with the country:

summarize

The SUMMARIZE function is used to find the distinct set of the Sales Territories and the countries.

Some other new functions are covered in the tip New DAX functions in SQL Server 2016.

Other enhancements

There are also some improvements behind the scenes:

  • The number of scans needed for non-empty calculations.
  • Multiple measures from the same table will be combined into a single storage engine query. This is called measure fusion.
  • When a measure is requested at multiple granularities, the query is sent at the lowest level and the other granularities are aggregated from this level. For example, if you have a query on the year, month and day granularity, only the day level data is fetched.
  • Elimination of redundant joins.
  • Strict evaluation of IF and SWITCH. Branches that result in False are no longer evaluated which means no storage engine queries are sent.
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





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 


SQL tips:

*Enter Code refresh code     



Thursday, July 20, 2017 - 6:58:45 AM - Greg Robidoux Back To Top

Hi Koen,

Congrats on your 100th tip.

Thanks for all of your contributions over the years.

-Greg


Learn more about SQL Server tools