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).
Returns the value when there’s only one value in the specified column, otherwise returns the alternate result.
SELECTEDVALUE ( <ColumnName> [, <AlternateResult>] )
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
IF ( <LogicalTest>, <ResultIfTrue> [, <ResultIfFalse>] )
Returns true when there’s only one value in the specified column.
HASONEVALUE ( <ColumnName> )
When a column name is given, returns a single-column table of unique values. When a table name is given, returns a table with the same columns and all the rows of the table (including duplicates) with the additional blank row caused by an invalid relationship if present.
VALUES ( <TableNameOrColumnName> )
Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.
AVERAGEX ( <Table>, <Expression> )
Returns the number of characters in a text string.
LEN ( <Text> )