THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

New function and the importance of variables in DAX #dax

I recently wrote a few articles about a new function in DAX and the use of variables. For those of you using Power BI or Azure Analysis Services, you always have access to the latest version of the language. Well, in reality I see that new features appears in Power BI Desktop first, and shortly after in Azure Analysis Services. I’m also working on something that will make it easy to recognize which functions is available in each product/version, but this will still require a few months…

So, what is new in DAX? The SELECTEDVALUE function! It’s only syntax sugar, so you can write:

SELECTEDVALUE ( Table[column], "default value" )

instead of

IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ), "default value" )

You will find more details and use cases in the Using the SELECTEDVALUE function in DAX article I recently wrote on SQLBI. My only concern is that this could increase the use of a bad practice, which is getting the value of the current row after a context transition. Something like:

Company := SELECTEDVALUE ( Customer[Name] )

AvgLength := AVERAGEX ( Customer, LEN ( [Company] ) )

instead of:

AvgLength := AVERAGEX ( Customer, LEN ( Customer[Name] ) )

If you think nobody would do that… yes, you’re right, not for the LEN function. But think again to all the calculations that requires a particular granularity and that you would like to include in a measure instead of a calculated column, and then you will realize that making this mistake is more common that you might think. Yes, in more complex expressions, of course.

The other topic that I covered in two articles is the use of variables, which you will find discussed in:

The basic idea is that you can simplify the syntax of complex DAX expressions by using variables, and the result you obtain is something that is much more similar to an M script rather than the classic nested sequence of function calls in DAX, as I described in another article, DAX coding style using variables. You can see the benefits of this approach in complex calculations, so this is not something that is very visible in short articles, and it will be the major change of future books and patterns about DAX (which is what I’m working on these days).

Published Tuesday, August 1, 2017 12:39 PM by Marco Russo (SQLBI)
Filed under: , , , ,



Tate said:

Looking forward to being able to recognize which new DAX functions are available in each new version of the SSAS engine, Marco!  Regardless, I'll always come to you guys to find out what they actually do! ;)

August 1, 2017 6:20 AM

Marco Russo (SQLBI) said:


August 4, 2017 2:18 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement