We are approaching the end of 2017, so it is a good time to review what happened in the DAX world this year, what we’ve been up to at SQLBI and what is going to happen in 2018.

New DAX functions in 2017

Microsoft released ten new DAX functions in 2017. We didn’t see much innovation in the syntax of the language. The IN operator really simplifies certain logical conditions, but if you already knew DAX in 2015 or in 2016, you are still good in 2017. Will we see something new in 2018? Maybe. In the meantime, let’s review the 2017 updates.

There are three functions currently available in Power Pivot for Excel 2016, Power BI, Analysis Services 2017, and Azure Analysis Services:

Another six functions are currently available only in Power BI, Analysis Services 2017, and Azure Analysis Services:

Finally, there is a new function available only in Power BI and Azure Analysis Services:

  • COMBINEVALUES: not documented yet. Concatenates several expressions separated by a fixed delimiter.

What SQLBI delivered in 2017

This year at SQLBI we delivered 34 public courses in three continents, several sessions and preconferences in 6 major conferences, and several SQL Saturdays and other community events.

We launched two books we had written in 2016: Tabular Modeling in SQL Server Analysis Services and Analyzing Data with Power BI and Power Pivot for Excel. The latter is a book about data modeling for Power BI (and Power Pivot) advanced users. If you read this blog, you probably understand this shorter description better than a longer table of content. So why a different title? Well, those who really need this book probably are not aware of it,  so the title had to be more generic. Call it marketing, if you like.

We also launched several video courses:

  • Introducing DAX (free!) – you have no excuse, you can learn the basics of DAX in less than three hours. And if English is not your first language, or you don’t like our accent, don’t worry: subtitles are included (as in all the other video courses).
  • Optimizing DAX – before optimizing your DAX code you need to know the language in depth, so usually you might consider this course after Mastering DAX. Both courses are based on our book, The Definitive Guide to DAX.
  • SSAS Tabular – based on our book, Tabular Modeling in SQL Server Analysis Services,, this video course covers all the topics related to Analysis Services with the only exception of DAX. Consider this video course also for Azure Analysis Services: the content is valid – Azure Analysis Services just has a different interface to schedule recurring activities. But the way you design a data model, manage APIs, plan partitioning and choose between DirectQuery and VertiPaq is identical.
  • Power BI Dashboard Design – learn how to design beautiful dashboards following 15 rules. You will learn principles that can be applied to any data visualization product, but all the examples are in Power BI and can be downloaded, too.

We updated and released several OKViz custom visuals for Power BI. Some of these visuals are required in the Power BI Dashboard Design course, so we wrote them for a purpose. However, all of them are completely free. Most visuals are available in the store and updated automatically in your reports, but if you want to use beta releases you should visit the okviz.com website.

What’s coming in 2018

Really, who knows?

Well, we have some plans:

  • A new data modeling course, based on our orange book (we shorten book names by using the cover color). It will be available as both a classroom course and a video recorded course.
  • We will be writing a new version of The Definitive Guide to DAX, to be published late 2018/early 2019. Yes, we are betting that there will be something new to say. If we are wrong, we’ll delay it.
  • We will be publishing a new version of the DAX Patterns updated for Power BI. Possibly with some more patterns. We are already behind on this project, so this will happen for sure. In 2018. Maybe first half? Hopefully.
  • We will keep on producing content, custom visuals, and tools for the BI community.

If you want to receive updates promptly without missing any news, go ahead and register to our newsletter.

We have very much enjoyed serving you, our reader as well as the broader BI community. Happy 2018!

CONTAINSROW

Returns TRUE if there exists at least one row where all columns have specified values.

CONTAINSROW ( <Table>, <Value> [, <Value> [, … ] ] )

DETAILROWS

Returns the table data corresponding to the DetailRows expression defined on the specified Measure. If a DetailRows expression is not defined then the entire table to which the Measure belongs is returned.

DETAILROWS ( <Measure> )

USERPRINCIPALNAME

Returns the user principal name.

USERPRINCIPALNAME ( )

GENERATESERIES

Returns a table with one column, populated with sequential values from start to end.

GENERATESERIES ( <StartValue>, <EndValue> [, <IncrementValue>] )

SELECTEDVALUE

Returns the value when there’s only one value in the specified column, otherwise returns the alternate result.

SELECTEDVALUE ( <ColumnName> [, <AlternateResult>] )

USEROBJECTID

Returns the current user’s Object ID from Azure AD for Azure Analysis Server and the current user’s SID for on-premise Analysis Server.

USEROBJECTID ( )

ERROR

Raises a user specified error.

ERROR ( <ErrorText> )

UNICHAR

Returns the Unicode character that is referenced by the given numeric value.

UNICHAR ( <Number> )

TREATAS

Treats the columns of the input table as columns from other tables. For each column, filters out any values that are not present in its respective output column.

TREATAS ( <Expression>, <ColumnName> [, <ColumnName> [, … ] ] )

COMBINEVALUES

Combines the given set of operands using a specified delimiter.

COMBINEVALUES ( <Delimiter>, <Expression1>, <Expression2> [, <Expression2> [, … ] ] )