DAX Enhancements in Analysis Services 2017

By:   |   Comments   |   Related: > Analysis Services Development


A new release of SQL Server Analysis Services (SSAS) means also new functions and functionality in the DAX language. In this tip, we’ll give an overview of the new improvements and enhancements for Analysis Services 2017.


With the release of SSAS 2017 a couple of new functions were introduced as well as a bit of new functionality. Keep in mind that everything described in this tip will be available in SSAS 2017 in the latest compatibility level (1400), but not necessarily in earlier compatibility levels. Unfortunately, the official documentation doesn’t mention in which compatibility levels a function is available. Similarly, new functionality will most likely be present in the latest versions of Power BI Desktop, but not in earlier versions and the same can be said about Excel.

In this tip we’ll use the WideWorldImporters Tabular model, which was created in the tip Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables – Part 3.

ERROR function

The new ERROR function allows you to raise your own error messages. Let’s suppose we want to calculate a sales amount measure in the WideWorldImporters model:

formula without error

However, it’s possible someone enters a unit price which is incorrect. Assume the company doesn’t sell items with a unit price over $1,000. We can embed some validation inside the formula which checks if the unit price doesn’t exceed the $1,000. If it does, an error is thrown so users are notified something is wrong with the source data.

formula with error function

If we would use the measure in Excel, we get the following result:

error in Excel

In Power BI the whole visualization errors out. You have to click on See Details to get the error message:

error in Power BI Desktop


This function will most likely be useful as an intermediate step for other, more complex formulas. GENERATESERIES generates a sequence of values with a start and end value. Optionally you can specify the increment. The output of the function is a table holding the sequence.

simple sequence

With an increment value of 2 specified:

simple sequence with increment

You can also use other data types, such as decimal number or currencies. Dates are supported as well:

sequence with dates

In this case, the result is similar to the CALENDAR function.

IN Operator

A nice addition to DAX is the IN operator, which in its simplest form is equivalent to the T-SQL counterpart. Let’s calculate the sales for two sales territories:

IN operator in action

This gives us:

IN operator in PivotTable

The CONTAINSROW function is equivalent to the IN operator:

containsrow example

We can verify this in the PivotTable:

containsrow vs IN

Unlike T-SQL, there isn’t a NOT IN operator. In DAX, to have the logical negation of the IN operator, you need to put the NOT function in front of the entire expression:

not in operator

Which yields the following result:

pivottable with NOT IN

You can also use multiple columns with the IN operator. For example, let’s calculate sales for November 2016 and October 2015.

IN operator with multiple columns

A thorough and detailed write-up on the IN operator by Marco Russo can be found in the article The IN operator in DAX.


Like GENERATESERIES, the SELECTEDVALUE function will most likely be used in more complex formulas and not on its own. The function returns the value for a column when a single, distinct value has been selected (or filtered). If it isn’t, you can supply an alternative result with an optional parameter. Let’s test this with a simple formula:

SELECTEDVALUE test formula

In a PivotTable, this gives the following result:

selectedvalue pivot

You could also use the formula to validate the input of a slicer. For example, only show a value in a visual if a single value on the slicer is selected:

another selectedvalue example

If one value is selected on a slicer, the result of Total Sales is shown:

single value selected

If nothing is selected, the slicer defaults to All, and the formula returns N/A:

all value selected

In Power BI Desktop, even when the slicer is configured for single selection, you can select multiple values when holding CTRL while clicking. With the formula, we can validate the input:

multiple values selected

TREATAS function

This function is a bit more special. TREATAS applies the result of an expression as filters to columns of an unrelated table. Let’s illustrate with an example. The Order table contains two dates: the Order date and the Picked date. Since Tabular doesn’t support two relationships to the same table, we need to create another date table for the picked dates. We can easily do this with a calculated table:

calculated table for picked dates

This expression simply takes a copy of the existing Date table. Then we need to create a relationship between the new PickedDate table and the Order table:

add relationship

Now we can use TREATAS to apply the filter on the Order date years to the Picked date years:

treatas example

If for example someone filters the orders for the year 2015, the formula returns the following result:

“return all sales for orders placed in the year 2015 and the orders are also picked in the year 2015”

Orders at the end of 2015 which were picked in 2016 are excluded. In Excel we get this result:

treatas pivot

As you can see, the result with TREATAS as a bit lower than the full total sales for a year, since some orders are picked in the year after. The grand total is a bit less as well, since there are blank values for some picked dates (which signifies orders which haven’t been picked yet).

Various Enhancements

Some smaller additions to DAX:

  • The UNICHAR function returns the Unicode character referenced by a numeric value. You could use this to create lightweight KPIs. A simple example using the thumbs-up emoji (number 128,077):
simple KPI using unichar
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck 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

Comments For This Article

get free sql tips
agree to terms