By: Koen Verbeeck | Comments (1) | Related: > Analysis Services Development
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.
You can also format your code by adding new lines or by indenting it.
There is also Intellisense included:
This also works for referencing tables or columns:
To help you out even more, there are error “squiggles” to point out mistakes in your DAX formula:
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:
The same is true for calculated columns:
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:
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):
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:
The result:
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:
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
- You can find a full overview in the DAX section of the article What's New in Analysis Services.
- More SSAS 2016 tips:
- Scenarios for Using Calculated Tables part 1, part 2 and part 3.
- Bi-Directional Cross-Filtering in Analysis Services Tabular 2016 - part 1, part 2 and part 3.
- Manage Extended Events for SQL Server 2016 Analysis Services in Management Studio
- DBCC Commands for SQL Server Analysis Service 2016
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
