**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!

“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(COUNTROWS(VALUES( **<Your Column Here>** ))=1,

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.

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.

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.

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.

Please help!

/Erik

Hi again,

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

/Erik

Yeah, I was about to reply and say “SUMX is your friend here” 🙂

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

Thanks!

Here ya go 🙂

https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

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

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

Yes. 🙂

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.

Thanks in advance

Daniel

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?

Its a resultant power pivot

You could do this with a normal pivot table by dragging Year into the columns area and then Sales into the values area.

This is a great example of Excel’s Internal Network Effect where your interest in Power Pivot has lead you to a feature that already existed in Excel apart from Power Pivot (I’ve experienced this many times).

https://powerpivotpro.com/2013/05/open-letter-to-my-friends-at-microsoft-the-power-of-excels-internal-network-effect/

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)

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)

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

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

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?

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

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

Hi! I have a question, please help me to solve thiIs!

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?

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

Excellent post! Thanks!

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

Thanks!

Simona Poggi

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

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.

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

IF([STATE]=”TELANGANA”,([Number_of_Price_FASAL]*1+[Call_Centre]*5+[IVR_ANSWERED]*5+[outbound_calls_answered]*0.5)/6.5,([Number_of_Price_FASAL]*1+[Call_Centre]*5+[IVR_ANSWERED]*5+[outbound_calls_answered]*0.5)/5.5))

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,

Marvellous – thank you

First, thanks for a great book (Power Pivot & Power BI)! I’m new to Power Pivot and that book went a long, long way to helping me get the concepts down.

I’ll be mildly surprised if you even answer this in a blog this old, but what have I got to lose?

I have a table with a column whose values are either “Sink” or “Shower” and I want to calculate a single measure that yields a result like this

WeightedAvg:= if([FixtureType] = “Sink”, (A*X + B*Y + C*Z)/(X+Y+Z), (D*U + E*V + F*W)/(U+V+W) )

I know that won’t work as is because [FixtureType] is a naked column. I thought I could “wrap” it in a LEN or LEFT function, but that didn’t work so I turned to Google and was delighted to find a discussion hosted by one of the authors of the book I have open in front of me while I slog through this. I now understand (thanks to your great graphic) why that doesn’t work either. There has to be a way to do this other than my current solution of having a weighted average sink flow and a different weighted average shower flow, one of which is always an error, depending on the filter value. It’s probably not relevant, but all the variables above (A, B, etc.) are already measures.

For example, A is [SinkFlow Hand], and SinkFlow Hand:=calculate([SinkFlow],DomesticSurvey[Fixture Subtype]=”Hand”) and SinkFlow:=CALCULATE(average(DomesticSurvey[BFA Flow]), DomesticSurvey[Fixture Type]=”sink”)

Thanks again for the great book. It was well worth the price.

hi all, i have question.. how to group the -ve balance… i did command as below, unfortunately the result only show +ve…

Flag Decimal of Variantion Commit = IF([Variant Commit]<0.00,"-VE","+VE")

Hello good evening,

I need small help. So I need to get how many same numbers repeats in Column A that matches same value in column B. Example How many times 38692 repeats in column A and has value of $5 in column B? Can anyone help me with that please

A B

38770 $10.00

38692 $5.00

38692 $5.00

38692 $5.00

38692 $10.00

38769 $10.00

38692 $5.00

38692 $5.00

38692 $10.00

$0.00

$0.00

38694 $10.00

$0.00

38694 $10.00

$0.00

Excellent post. Thank you so much Rob! 🙂

Hi, i’m new to PowerPivot and need some help here. I have 3 columns comprising of integers (say 1 to 20, and 100 – which implies “ignore” – i will elaborate in a while).

I would like to create a new calculated column to store the middle integer of the 3 columns by throwing out the smallest and largest integer:

1,3,2–> 2

2,2,3 –> 3

3,3,2 –> 3

1,1,1 –>1

If any of the columns comprise of 100, we will ignore this column totally:

1,100, 2 –> 2

1,1,100 –>1

100, 100, 2 –> 2

But if all three columns comprise of 100, we will return 100:

100, 100, 100 –> 100

Is there an easy way to do it in powerpivot? (I have the flexibility of replacing 100 by 0 if that helps)

Thank you and regards

Can anyone tell me why I get an error with this:

=CALCULATE(sumx(‘Oppty Detail'[# of Opportunities])+0,’Oppty Detail'[Fiscal Week] =[ReportingDate])

ReportingDate is a concatenated field that contains ‘FY17-Mar-Wk3’

Rob,

I know this is very far after this post, but I have a question about the multiple rows part.

Say I have a subtotal –> This is why the multiple rows are being returned

and I want to be able to evaluate this subtotal in the same output. How would I do this? Is there a post that this is already covered in?

Colum1 Colum2 NewColumn

5 0 5

6 10 0

0 5 0

5 5 0

New column formula written – =IF((A3-B3)<=0,0,(A3-B3))

How can i write in power pivot ? I tried using the same syntax but not getting the required result like in excel