DAX – The Many Faces of VALUES()

Guest Post by Scott at Tiny Lizard

Maybe it is a sign of where I am on the Geek Scale compared to Rob, but where he considers EARLIER() to be a pretty hard function to understand, it just doesn’t bother me.  At least it seems to have just one purpose in life.

Now, the VALUES() function on the other hand… well, that’s just some messed up stuff right there!  Not only does nothing about it feel natural and intuitive to me, but it also seems to behave in completely different ways depending on how and where it is used.

Basically, every time I use it, I feel like I either got lucky, pulled a fast one, and that I’m a dirty cheater.  So, at least I got that going for me.

Let’s look at some of the various usages.

RETURN UNIQUE VALUES

Much like the ALL() function (or I suppose identical to the ALL() function), the VALUES() function returns only distinct values. Against a column of colors { Red, Red, Red, Blue, Red, Red } both VALUES() and ALL() functions will return a table consisting of one column that is { Red, Blue }.  The difference between these two functions is that VALUES() works within the original filter context, where ALL() cares not about you or your silly filter context… it only cares about freedom.  Freedom from the bounds of filters and the tyranny of… wait, what?

Anyway, did I just read that it turns a column into a table?  Easy there champ!  That topic is quite a ways off!  At least 4 words…

CONVERT COLUMN TO TABLE

You will find this totally surprising, but one of the things VALUES() does is convert a column into a table.  This is pretty useful for… well, mostly calling functions that want a table. Say… SUMX() or FILTER() or CALCULATE() or COUNTROWS() or TOPN() or uh… apparently there are a lot of functions that can work on tables.

Just beware that, based on the previous section, this is not a straight conversion from column to table… as only the unique values will live on.

BRING THE FUNKY TRACK BACK

Once you have called an ALL() to remove filters, you can always bring them back with a call to VALUES().  You kill the poor defenseless filters, but then you are nice enough to bring them back from the dead.  Just like a… uh, um, thing, that can bring stuff back from the dead.

   =CALCULATE([Measure], ALL(Calendar), VALUES(Calendar[Year]))

Even though the ALL(Calendar) stripped away all filters on the Calendar table… the VALUES(Calendar[Year]) will bring back the filter from the original filter context on the Year column.  This will behave identical rather similar to

   =CALCULATE([Measure], ALLEXCEPT(Calendar, Calendar[Year]))

but the other style, according to my calculations… makes you 7% more DAXalicous, DAXariffic, and DAXsome.  Oh, and if you really want to bring the funky track back, that is here.

INSPECT THE FILTER CONTEXT

The VALUES() function gives you a way to say “Hey, am I filtering on such and such” ?  eg:

   =IF (VALUES(Calendar[Month]) = “April”, “April is here!”, “Sadness.”)

Which I guess isn’t shockingly dissimilar to re-establishing the filter context in Calendar[Year] above, except this is more of a “read” than a “write”.  Maybe I am stretching there?

Anyway, if I am not mistaken, this will blow up if the filter context has more than one row in it.  Let’s see…

Calculation error in measure Calendar[TestMeasure]: A table of multiple values was supplied where a single value was expected.

Yep.  It blew up alright.  This is caused by any row in your pivot table that has more than 1 unique value for Month, such as grand totals.

You need to protect the call to VALUES() with a HASONEVALUE or HASONEFILTER or something of the sort. 

   =IF (HASONEFILTER(Calendar[Month]),
        IF (VALUES(Calendar[Month]) = “April”, “April is here!”, “Sadness.”),
        “Woah.”
   ) 

This will print “Woah.” on cells with multiple months instead of blowing up, just as our forefathers intended.

CONVERTS COLUMNS TO SCALARS

This is easily the weirdest thing VALUES() does.  If you call VALUES() on a column that has exactly 1 value (or I suppose multiple values that are all identical) then it returns a value that can be used as a scalar.

Scalar.  It is from the Latin word Scalapatipatimousia meaning “Scott makes stuff up”.   Err, no.  That’s not it. I have no idea what the origin of the word is, but it is just fancy talk for “a simple value”… like “Scott”, 7, or “4/1/2012”.  Any single value.

Looking back at the previous point then, it makes sense that I am allowed to test VALUES(Calendar[Month]) against “April”… since the call to VALUES() will convert that column into a simple value…  and then, it is like testing “Scott” against “April”.

AS AN AGGREGATE

You know how you can’t write a measure (calculated field in 2013, grr) like… =MyTable[MyColumn] ?
Because the engine would be like… “Uh… Scott?”  (Note:  feel free to substitute your own name when pretending the engine is conversational) “Uh… Scott?  I’m looking at multiple rows in this pivot cell, and you only referenced a column.. uh, how am I supposed to know WHICH row in that column you want?  And also, why don’t you bring me flowers any more?”

You must wrap your column references in an aggregate function… like SUM, MIN, AVERAGE, whatevs.  However, in the cases where you claim “well, I know there is only 1 value, so I can just use ANY of those…”, well then, in those cases I claim, you can use VALUES() instead.

It will convert that one and only value to a scalar (just like all the other aggregate functions).  And has the nice side-effect (kinda) that if you are wrong… if it ever gets MORE than 1 row… you will know.
In all its gory error’y detail, you will know.

So, be REAL careful of Grand Total cells, since they tend to have multiple rows.  You can, of course test for that with HASONEVALUES() or ISFILTERED(), etc.  Note that it won’t crash and burn until RUNTIME.  Check Formulas button will still love you, just like I do.

Wrap Up

Rob points out that you can also do VALUES(myTable) to bring the funky track back on ALL the columns of the table.  I prefer not to contemplate such things, since my head already hurts.

There may be even more ways to use VALUES(), but we can hope not.  In the mean time, instead of pondering the many faces of values, I am going to watch The Many Faces of Guilty Dogs.