skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile

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.

This Post Has 14 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 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.

  4. Need some help using an IF statement in a measure! or if there are other solutions to achieve what I am trying to do would be great!

    I have a table with volume of messages [see columns labelled Received and Sent] broken out by Shortcode by Client. I am trying to calculate two measures. The first

    1 . The average cost of a message [See tiered rate in data below]
    2. T-Mobile counts calculated as [Sum of Received & Sent], Filter on [Billingtype]=”STD”,[Operator]=”T-Mobile”, [T-Mobilelegacy]=”FALSE”
    3. Total payable to T-Mobile [ measure 1 x measure 2]

    Shortcode Client Operator Period Billingtype T-Mobilelegacy Received Sent
    11000 Beanfield T-mobile 2018/2 STD FALSE 1,520,000 547,089
    33000 Startelecom T-mobile 2018/2 FTEU FALSE 1,379 885,226
    555669 Shoppers Drugmart T-mobile 2018/2 STD TRUE 3,278 76,774
    555669 102.5 FM T-mobile 2018/2 FTEU TRUE 1,102 28,561
    66666 103.1 FM T-mobile 2018/2 STD TRUE 2,113,710 217,574
    88888 Jack Mobile T-mobile 2018/2 STD TRUE 191,938 995,000
    625625 Fandrive T-mobile 2018/2 FTEU FALSE 6,746 93,857
    825227 Garnier T-mobile 2018/2 STD FALSE 775,000 32,798

    Tiered rate
    1 -3,000,000 0.0085
    Greater than 3,000,000 0.0071

    Here’s what I have so far:

    Calculated Measure
    1. Due to T-Mobile:=Calculate([Sum of Received & Sent],Transactionstable[Billingtype]=”STD”,Transactionstable[Operator]=”T-Mobile”,Transactionstable[T-Mobilelegacy]=”FALSE”
    2. AvgCost=(IF([Due to T-Mobile]<=3,000,000,25,500,([Due to T-Mobile]-3,000,000)*0.007)+25,500)/[Due to T-Mobile]

    When I multiple the two measures the amount gets calculated correctly but only in the sum total. On a client level the amount is not correct and I know this has to do with the fact that IF is not an iterator. Can anyone review and provide some guidance/solution?

    Thanks very much!

  5. I believe VALUES can not be used with dynamic tables, can it? Like in this example:

    Row ( “pp”, VALUES (
    FILTER (
    “Cumulative”, SUM ( Sales[sAmount] ),
    “sCumulativeAmount”, CALCULATE (
    SUM ( Sales[sAmount] ),
    FILTER ( ALL ( Sales[sDate] ), Sales[sDate] <= MAX ( Sales[sDate] ) )
    [sCumulativeAmount] = 0

Leave a Reply

Your email address will not be published. Required fields are marked *