PowerPivotPro is Coming to Phoenix

February 20 - 22, 2018

Registration for 2018 Public Training is now open!


**Use the discount code “3ORMORE” when signing up 3 or more people.

FEBRUARY 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Kellan Danielson

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Kellan Danielson – PowerPivotPro Partner and Vice President of Client Services
  • You don’t need to be an IT professional – most of our students come from an Excel background

FEBRUARY 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!


  • This advanced DAX training class is taught completely in Power BI Desktop.
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language.
  • Taught by Ryan Sullivan – Principal Consultant.
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms.


Level Up Series: Power Query for Excel & Power BI

Instructor: Krissy Dyess

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop.
  • Taught by Krissy Dyess – PowerPivotPro Principal Consultant and Phoenix native!
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities.
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges.
PowerPivotPro Logo

Guest Post by Scott at Tiny LizardMany Values

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.


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…


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.


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.


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.”),

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


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”.


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.

  Subscribe to PowerPivotPro!


This Post Has 10 Comments

  1. Hi Scott, just wanted to let you know that I really enjoyed this post! Highly informative and amusing – nice combo. Enjoyed The Many Faces of… too 🙂

  2. Hi,

    I am Dutch and lose a bit of the content due to the writing style used. I do understand that humor is involved :).

    Would it be possible to give a brief summary (could be done in English) without the irony?


    1. I admit the content to humor ratio is a bit low 🙂 The single line summary would be “The VALUES() function returns a table of the unique values from the original filter context (and can be used as a scalar if just 1 value is specified)”. And it gets interesting from there based on how/where it is used.

      Is there a specific section of the post I can help with?

      1. Thanks for the explanation! intuitively I guess values will be used to retrieve subsets of data where, in an ‘ACCES’ environment, one would use queries with output ‘grouped by’ and ‘is not null’ statements. Imagine, you own Amazon.com and you would like to know which movies quoted more than once in Januari [or any other month] had an increase in items sold in March [or any other month, Values() would give the right subset to calculate the number of items sold.

        1. Regarding IS NOT NULL:

          VALUES(table[field]) can actually return a blank value (similar to NULL), if you want to avoid this you should use DISTINCT(table[field]).

  3. Sigh. I really don’t think its emphasised enough in the literature that VALUES is evaluated in the original filter context. Thanks for saying so explicitly, turns out I’m not crazy.

Leave a Comment or Question