DAX – Precedence Project Part Two: VALUES()

OK, picking up from part one…

Let’s start with a simplified version of last post’s pivot – remove one of the row fields, and all of the measures but the base Total Sales measure:

Now let’s add a measure that sets Year to ALL:

[ALL Year Sales] = [Total Sales](ALL(Periods[Year]))

Note that all of the orange cells have the same value – they are all Q1 cells and differ only on Year, so the all Year measure means each orange cell will display the total sales for Q1 across all years.  So the green cells all add up to the value in each orange cell.

Rule #3:  VALUES() Selectively “Shuts Off” ALL()

OK, we’ve played with VALUES() a few times on the blog, most notably here and here.  But previously, we’ve never used it like we are going to use it this time.  We are going to use it to preserve the original filter context and “fight back” against ALL().  Exciting huh?

Let’s add another of our previous measures back to the pivot – the one that applied ALL() to the entire Periods table:

[ALL Period Sales] = [Total Sales](ALL(Periods))

OK, now let’s add VALUES() to the mix in a new measure that has ALL applied to the whole Periods table, and VALUES applied to just one column in Periods:

[ALL with VALUES] =
[Total Sales](ALL(Periods), VALUES(Periods[FiscalQuarter]))

This time, again the Q1 cells are all the sum of every Q1 in the base Sales measure.  So the VALUES function, applied to the FiscalQuarter column in this measure, overrode the ALL which was applied to every column in the Periods table (including FiscalQuarter!)

So this new measure differs from the pure [ALL Period Sales] measure at the Quarter level.  But note the blue cells.  It’s still identical to [ALL Period Sales] at the Year level.

So is ALL() still “winning” at the Year level?  No.  It’s just that, at the Year level, VALUES(Periods[FiscalQuarter]) returns  the list of all four quarter values, because all four Quarter values truly due correspond to that cell’s coordinates in the pivot (the subtotal for the Year does indeed imply all quarters).

Neat huh?

Clarification/Correction

Derek pointed out in a comment that the two values in blue actually are NOT equal.  They are off by $281.  This is because there are blank values for year – some noise in the data – and I have filtered those out using the little Row Labels dropdown:

If I clear that filter and allow blanks to show up in the pivot, I get:

Note that the two values in blue are still off by $281.  But the mysterious missing $281 is indeed accounted for by the blank year.

So…  the pure ALL(Periods) measure catches that $281.  That’s expected.  What’s unexpected, at least initially, is that the ALL(Periods), VALUES(Periods[Fiscal Quarter]) measure MISSES that $281.

Here’s why:  note that there’s only a blank Quarter under the blank year.  There are no blank quarters associated with the blue cells.  So that $281 is omitted.

Great catch Derek, this was awesome.  I probably should spend a post just explaining where those blanks Years and Quarters came from.

Back to the original post…

We can even confirm that VALUES() beats ALL() in a heads-up fight.  Rather than use ALL() against a whole table and VALUES() against one column, let’s use them both against the same single column:

[All vs VALUES Heads Up] =
[Total Sales](ALL(Periods[Year]),VALUES(Periods[Year]))

Hey look!  Our new measure returns the EXACT same results as the base sales measure.  VALUES completely shut off ALL() in a head to head fight.

Rule #4:  VALUES() Wins Even if the “Targeted” Field Is Not on the Pivot!

Maybe this one isn’t so much a precedence rule as it is surprisingly cool.  Let’s try using VALUES() against a column from the Periods table that we don’t have on the pivot…

Let’s try that [Period Num] column.  Notice how it uniquely identifies each period, regardless of Year?  It’s the unique key for this table, so if VALUES() works, that *should* beat ALL() even if ALL() is applied to the entire table.

Let’s try it:

[ALL then VALUES of field not on pivot] =
[Total Sales](ALL(Periods),VALUES(Periods[Period Num]))

…and the result is IDENTICAL to the base sales measure.  The ALL() is completely overridden.  Cool huh?  VALUES operates against the underlying tables, and merely takes into account the filters from the current pivot cell.

Good thing to know.  I’ve been taking advantage of that for a long time, it is very helpful.

OK, this post has run long so I will save ALLEXCEPT for the next one.