“I’m Dan Marino, and IF() anyone knows the VALUE() of protection, it’s me.”

Boom!  An Ace Ventura quote finally graces the blog.  Yes folks, that fine work of American cinema did indeed feature Dan Marino reprising his real-world ad for Isotoner.

(UPDATE:  I had the val_if_true, val_if_false arguments reversed in my sample formulas, even though I had them correct in Excel.  I’m rusty .  Fixed now.)

But more importantly, I wanted to revisit this little two-function combo:  IF(VALUES()).  I’ve covered it before a few times, but generally in the context of covering something else.

And since this is one of my favorite techniques, I think it deserves its own dedicated post.  A series of posts, actually.  Let’s dig in.

### In Measures, You Can’t Just use IF()!

In regular Excel, the IF() function is a familiar tool to most of us:

IF(logical_test, value_if_true, value_if_false)

In PowerPivot, you can use precisely that same version of IF() in calculated columns.  So, for example:

IF(Table1[Column1]>6, 0, 1)

In a measure formula, however, you cannot do that.  If you do, you will receive the following error:

To better understand why that happens, let’s get ourselves an example…

### “Whoa, did you say DimDown???”

Yeah that’s right, the Great Football Project is back after a long hiatus! I had a phone call yesterday with a Pittsburgh Steelers fan.  You know Pittsburgh?  It’s a drinking town with a football problem.

Anyway, he requested a return to the football project, under the umbrella of “how do the Steelers win another Super Bowl?”  Given that I lived in Seattle for 13 years, and witnessed his Steelers literally “steel” a Super Bowl from Seattle via lopsided referee calls, I decided that we should look at what should have been the Seahawks’ championship season:  2005.

[Pct Successful Plays] is a measure that I’ve been developing with Hugh Millen.  I’m not going to reveal the “secret sauce” behind said measure, but you can see that in Week 21, when the Seahawks played Pittsburgh in the title game, only 31.2% of their plays were successful – well under their 38.1% total for the year, and their 6th-worst percentage of the year.

### Applying Different Formulas in Different Circumstances

Now let’s say that I want to return a different value for this measure in Week 21, which is Super Bowl week.  You know…  I want to “correct” for bad referee calls.

I’ll create a new measure, [Corrected Pct Successful Plays].  In normal cases, I want that measure to just return the same value as would be returned by the original [Pct Successful Plays] measure.

But in week 21, I want to double it.  Just to set things straight.  (In real business, there are many legitimate examples of this technique, like applying a different formula in the West region versus the East region of a sales territory).

If I just use IF() by itself, I will get the error I showed in the previous section.

But if I nest VALUES() inside of the IF(), it will work!

IF(VALUES(Schedule[WeekNbr])=21, [Pct Successful Plays]* 2,
[Pct Successful Plays] )

Neat, huh?  Returns the same value as [Pct Successful Plays], except it doubles in Week 21.

### A Return to the Golden Rules of DAX Measures:  No Naked Columns!

So why is VALUES() required?  Because you cannot have “naked” columns in your measures.  This is one of the “golden rules” for DAX measures that I first introduced over a year ago – see this video for an explanation.

If you’re not interested in watching that video right now, fine, just take my word for it:  in most places, you cannot just include column references in your measures, you have to wrap them in a valid function.  (And go watch the video later, as it’s a concise explanation of the fundamentals of DAX measures, and something I cover on every consulting/training engagement).

VALUES(Table[Column]), however, IS legal in a measure, and VALUES returns the list of values for the specified column in the current context.  So in the last row of the pivot above, it returns 21, and the formula becomes IF(21=21, 2 * [Pct Successful Plays], [Pct Successful Plays]).

Make sense?  OK, good, because I am lying to you.  There’s one more trick I have to show you, as the formula above actually STILL doesn’t work.

### But…  IF(VALUES()) only works when there is only a single value!

OK, here’s the formula from above, repeated here:

IF(VALUES(Schedule[WeekNbr])=21, [Pct Successful Plays] * 2,
[Pct Successful Plays])

On each individual row of the pivot above, [WeekNbr] DOES have a single value (1, 2, 3, …  21).

There are actually many cases, however, where [WeekNbr] does NOT have a single value.

For instance, in the Grand Total cell of the pivot:

In the grand total cell, [WeekNbr] is NOT a single value.  It is actually ALL weeks.  In that case, the VALUES function returns a full column of values, and comparing a whole column to a single value doesn’t work out too well.

I like to visualize that problem in my head:

IF(VALUES(Column)) yields an error when there is more than one value of Column

And by the way, that’s not an error that you will just see displayed in the pivot’s grand total cell.  It will “tank” the evaluation of the entire pivot:

### Protecting against multi-value situations

Protecting against this is pretty simple.  You just use another IF() to “guard” your IF(VALUES()).

The “guard” if is highlighted below, wrapped around the original formula in normal font.

=IF(COUNTROWS(VALUES(Schedule[WeekNbr]))=1,
IF(VALUES(Schedule[WeekNbr])=21, [Pct Successful Plays] * 2,
[Pct Successful Plays]),
[Pct Successful Plays])

In essence, you never let your IF(VALUES()) get evaluated unless there is only one value!  If there is more than one value (as there is in the grand total, or in a subtotal), the formula above just returns the original measure (that’s the “[Pct Successful Plays]” at the end).

### Making it simple (seriously, just follow the pattern!)

A lot to digest?

It may seem bad, yes.  But you don’t actually have to grasp it fully.  You can just treat this as a “pattern” that you use.

For example, here is the formula turned into a pattern.  You just substitute your columns and values in the highlighted spots:

IF(VALUES( <Your Column Here> ) =  <Test Value Here> ,
<Result if True> ,
<Result if False> ),
<Result for subtotals and grandtotals> )

### The stage is set for some serious fun!

OK, with all of that covered, that sets us up for a series of quick, simple, and powerful techniques that I can share in the next blog post.

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 30 Comments

1. johncon says:

ummm… It’s been a long day, a long slow commute home via sleet covered roads and it is REALLY nice to see this return to valuable examples and the Great Football project, but something isn’t sinking in here.

For starters – IF(logical_test, value_if_true, value_if_false). Ok, I understand that.

Next is your statement: “But in week 21, I want to double it.” Just to set things straight, ie if Week =21, then x 2.

And how do you do this? Well it starts with the measure: IF(VALUES(Schedule[WeekNbr])=21, [Pct Successful Plays],
[Pct Successful Plays] * 2)

This is part I have problems with (especially with the graphic you showed immediately under it).
IF week = 21 then you want to double it. And how is IF written out? Logical test, value IF TRUE, value IF FALSE.
So.. if you write out… (removing syntax) IF weeknbr EQUALS 21, then Pct Successful Plays, IF NOT then Pct Successful Plays x 2.
Wait.. what. I thought you WANTED to double Week 21. In this statement, at Week 21 should equal – Pct Successful. NOT Pct Successful x 2.

Looking at the graph, EVERYTHING that WAS FALSE, ie not=21, remained the same, but everything that was equal to 21 was doubled, yet the IF Syntax had True/False backwarks. Man.. you have some ‘splaining to do.

1. Ooops, you got me. Fixed. I had it correct in Excel/PP, but bungled it in the blog. Sorry about that, but thanks for catching it so quickly.

2. Erik Olsson Dibbern says:

Hi Rob,

I actually think this is the greatest formula ever!If it only would work with the grand totals as well that is! Is it possible to create a new measure that sums up the If(Values()measure? Like a SUMX or just a plain SUM?

I have used this If(Values() measure to create interactive reports where the users can change a slicer setting to change the condition in the IF statement.

But I need the grand total to be the sum of the different values.

/Erik

3. Erik Olsson Dibbern says:

Hi again,

I actually solved it a mintue after writing the crying-out-for-help-post. SUMX did the trick!

/Erik

1. powerpivotpro says:

1. Kellan Danielson says:

Could you explain how to use the SUMX function to accomplish this?

Thanks!

1. powerpivotpro says:
1. I’m still struggling… I want to create a measure to use as a slicer so that if the balance for an account is -£3000 say “less than -£3000” if it’s 0 then say “Zero Balance” etc but I can’t work it out, I’ve tried sum, sumx, values but I can’t get my head around it! am I missing something?? Thanks, Phil

4. lenzy says:

can you use “IF” “AND” in powerpivot?

1. powerpivotpro says:

Yes.

5. Daniel says:

Hi, im new to this site, and let me tell you its amazing.
My question is, i have a field named “sales”, wich contain the sales of my clients and i need to separate the field by year, so i can create a “calculated field” called variation
Example
Year | Sales
2010 \$15
2010 \$10
2011 \$5
2012 \$25
2012 \$10
2013 \$5
2013 \$5
2013 \$50

What a want to do:
Year | Sales 2010 | Sales 2011 | Sales 2012 | Sales 2013
2010 \$15 \$0 \$0 \$0
2010 \$10 \$0 \$0 \$0
2011 \$0 \$5 \$0 \$0
2012 \$0 \$0 \$25 \$0
etc, etc.

Is this possible to do?
or maybe you can suggest a different way of doing it.

Daniel

1. powerpivotpro says:

Your desired results – the second table you posted – is that another table in Power Pivot that you want, or is it a resultant pivot table?

1. Daniel says:

Its a resultant power pivot

1. Daniel says:

Hi Tim, i know (i don’t want to sound rude or anything) i can do what you are saying, the reason i want to separate into diferent columns, is because i want to get the variation (sales 2013/sales 2012 – 1) in the excel spread sheet. and i can’t do that if i have just one field (Sales)

2. I see, I thought that might have been too simple

What about using the CALCULATE function to create three separate measures like this:
CALCULATE(SUM(Table[Sales]),Table[Year]=2010)

3. Eman says:

Tim, you list a calculation below, but what if you would like to see a YTD calculation for a specific year? I’ve tried TOTALYTD and DATESYTD, but both scenarios display the total year amount, rather than the YTD measure regardless of the row (which is year-quarter, i.e. 2015Q2).

6. Daniel says:

Hi, i got another question, what happens if i want to make just what you did, but im comparing year and month, for example:

=IF(VALUES(SALES[YEAR])=VALUES(CURRENCY[YEAR]) -> comparing year
&&VALUES(SALES[month])=VALUES(CURRENCY[month]), ->comparing month
SALES[SALES]/CURRENCY[CURRENCY],”ERROR”) ->if found do the math, if not put 0

7. Sofya says:

Thank You for the entry!
Did I understand right:
total/subtotal row ruin if(value) function despite the fact that there is no total row in the power pivot (it doesn’t add auto sum or total of pivottable)

Also,I follow You instructions but my excel (2013) did not show any error and work will with just if(value({})=21;{}*2;{}).Did they solve this problem in excel 2013?

1. Sofya says:

Sorry …and work well with just if(value({})=21;{}*2;{})

8. Rock says:

Thanks for the solution, But when try to multipl with -1 value into measures, then expected result not getting…
for example:
=IF(COUNTROWS(VALUES(Schedule[WeekNbr]))=1,
IF(VALUES(Schedule[WeekNbr])=21, [Pct Successful Plays] * -1,
[Pct Successful Plays]),
[Pct Successful Plays])

I am not getting result…
can you help me on that..

9. Ankita says:

Suppose I have 100’s of item in my inventory and the stock changes as per the demand.
I want to put a slideshow in my store showing which all products are available. I want to synchronize it with my stock, i.e when stock is 0 automatically the images should not be shown in slideshow.

eg: I have 4 items and their stocks in excel:

A 10
B 2
C 1
D 5

currently all the products images re shown in slideshow, but as soon as someone buy item C, it wll change to 0, thus image should be disappear form the slide show.

Can Powerpoint and excel be interfaced with each other for this purpose?

10. Hi , make me understand another Enclave in DAX , thanks !

11. Simona Poggi says:

Excellent post! Thanks!

12. Can i use the function HASONEVALUE (xx) instead of COUNTROWS(VALUES(xx))=1?

Thanks!
Simona Poggi

1. Rob Collie says:

Yes absolutely. That function was not available in DAX back when this post was written years ago

13. GMF says:

This is almost what I need, but I’m not smart enough to take it the final mile.

I have a single audit log file. I want to summarize what changes a user made. The transaction ID ties “old” and “new” values together.

Transaction ID Location Unit of Measure Order Qty In Stock
——————- ———– ——————— ————– ———–
123 K123 Box 5 Yes
123 K123 Case 1 Yes
456 H002 Each 4 No
456 H002 Each 3 No

My report would show:
Location Unit of Measure changes Order Qty Changes Stock Changes
———– ——————————— ————————– ——————–
K123 1 1
H002 0 1

But I’m just not figuring out the DAX logic to make this happen – is it CALCULATE with a filter, an IF statement with COUNTROWS? I can’t get this to work.

14. Calculated Field : I have to use a IF condition on a text field to look for a particular value and then accordingly give the values. How to do it ? STATE is the column i need to perform IF on and all the rest are numeric fields

15. Allly says:

Hi,

I am trying to write a logical formula in Powerpivot window which executes correctly in excel but not in powerpivot window as calculated column.

e.g

Registration year= 2012
Min fiscal yr = 2012
max fiscal yr = 2015

Criteria:

Registration year if falls under min and max range the value of the calculate column should be new.

formula:

=IF(AND([RegistrationYear]>=[MinFiscalYear],[RegistrationYear]<=[MaxFiscalYear]),"New","OLD")

Note: Minfiscal and maxfiscal are the measures in powerpivot window. I am using these majors in calculated column in powerpivot.

thanks,

16. John says:

Marvellous – thank you