Soapbox Series: Adding No VALUE(S)

[Greg_Deckler]

Introduction

OK, first I want to state that I am not a big "best practices" kind of guy. By this I mean that I see little value in discussing "best practices" because there are few true absolutes in life and always zillions of exceptions. All discussing "best practices" generally does is waste a lot of people's time and effort and; bonus, leads to lots of grinding and gnashing of teeth. It's unproductive.

That said, there is something that I have noticed in the forums lately, although it seems to have calmed down somewhat, and that is the, in my opinion, overuse of the functions VALUE and VALUES. People seem to just throw these functions into DAX formulas randomly for no reason and it honestly mystifies me. At best, the use of these functions has no impact on the calculation. At worst, it causes a problem. I am not saying that there is never a reason to use VALUE or VALUES but their use should be incredibly rare in my opinion. Here's why.

VALUE

The VALUE function does one and only one thing. The VALUE function converts text to a number if it can. So think VALUE("100") returns the number 100. Now, first of all, I question why you would have a text value that you would need to convert in this way. Given that you can specify the data type of columns and measures within both Power Query (columns) and Power BI Desktop (columns and measures) one must wonder of the need for doing this on the fly. But second, and perhaps more importantly, DAX already does this kind of conversion automatically. So, if you write a column like:

Column = "1" + 1

This column will indeed return the number 2. No need for VALUE.

VALUES

Next up is the VALUES function. The VALUES function actually has two forms. The first form is that you specify a table as the parameter. The second form is that you specify a column as a parameter.

If you specify a table as a parameter, VALUES returns the rows of the table and, crucially, duplicate rows are preserved. Think about that for a minute. So in the event that you would want to return the rows of a table, just reference the table like:

New Table = 'Table'

No need for:

New Table = VALUES('Table')

What about the column form? Well, the column form returns the distinct values in a column, so if you had data like:

Column

One

Two

Three

One

Two

Then VALUES('Table'[Column]) would return One, Two, Three in a single column, three row table. OK, useful. Except that DISTINCT does the same exact same thing and can handle either references to a column in a table (like VALUES) or any valid table expression. So, DISTINCT is waaaaayyyyy more flexible. 

Now, one difference between VALUES and DISTINCT is that VALUES does have a tendency to return a blank row when referential integrity is not preserved. The easiest way to see this is to have a relationship between two tables. Say you have a table like:

Table1

Column

One

Two

Three

and another table like:

Table2

Column

One

One

Two

Two

Three

Four

And you setup a 1->* relationship between these two tables and then do something like VALUES('Table1'[Column]) then you will get back One, Two, Three and BLANK. Which probably 99 times out of 100 is not what you want. You probably don't want that blank. Well, lucky for you, DISTINCT doesn't return that blank.

Conclusion

So, I am not saying that there is NEVER a reason to use the VALUE or VALUES functions. What I am saying is that in the vast majority of cases that I have seen, the VALUE and VALUES functions really add very little actual value to formulas because they are either not needed or should be replaced by a better function, like DISTINCT. Plus, as I pointed out in my Performance Tuning DAX article, the use of VALUE and VALUES is costing you performance cycles. So, in my humble opinion, the VALUE and VALUES functions showing up in DAX formulas should be extremely rare.

OK, stepping down off of my soapbox now.

AddingNoValue.pbix