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

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

DAX Enhancements in Analysis Services 2017


By:   |   Last Updated: 2018-03-06   |   Comments   |   Related Tips: > 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

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

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

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.

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

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


Last Updated: 2018-03-06


next webcast 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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools