By: Koen Verbeeck | Comments | Related: > Analysis Services Development
Problem
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.
Solution
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.001.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.002.png)
If we would use the measure in Excel, we get the following result:
![error in Excel](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.003.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.004.png)
GENERATESERIES function
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.005.png)
With an increment value of 2 specified:
![simple sequence with increment](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.006.png)
You can also use other data types, such as decimal number or currencies. Dates are supported as well:
![sequence with dates](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.007.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.008.png)
This gives us:
![IN operator in PivotTable](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.009.png)
The CONTAINSROW function is equivalent to the IN operator:
![containsrow example](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.010.png)
We can verify this in the PivotTable:
![containsrow vs IN](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.011.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.012.png)
Which yields the following result:
![pivottable with NOT IN](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.013.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.014.png)
A thorough and detailed write-up on the IN operator by Marco Russo can be found in the article The IN operator in DAX.
SELECTEDVALUE function
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.015.png)
In a PivotTable, this gives the following result:
![selectedvalue pivot](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.016.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.017.png)
If one value is selected on a slicer, the result of Total Sales is shown:
![single value selected](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.018.png)
If nothing is selected, the slicer defaults to All, and the formula returns N/A:
![all value selected](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.019.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.020.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.021.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.022.png)
Now we can use TREATAS to apply the filter on the Order date years to the Picked date years:
![treatas example](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.023.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.024.png)
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](/tipimages2/5347_dax-enhancements-sql-server-analysis-services-2017.025.png)
- According to the official documentation, the syntax for the table constructor is introduced in Analysis Services 2017. However, it seems it could already be used before, as this article demonstrates. The tip Scenarios for Using Calculated Tables in SSAS Tabular 2016 or Power BI Desktop – Part 2 also gives other options to create small tables with DAX.
- The following functions now support string data types: MIN, MAX, MINX and MAXX.
Next Steps
- You can find an overview of the new DAX functionality in SSAS 2016 in the tip Enhancements for the DAX language in Analysis Services 2016.
- The model used in this tip can be downloaded here.
- You can find more Analysis Services tips in this overview.
- The official documentation about the new DAX features can be found here.
About the author
![MSSQLTips author Koen Verbeeck](/images/Koen-Verbeeck-2018-2.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips