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

Cast of Superbad:  Clearly watching a video on amazing PowerPivot DAX techniques

“I mean, IF() I’m paying top dollar, I want a little production VALUE()”

I bet you thought I was out of movie quotes for IF(VALUES()) didn’t you!  If I had a fake ID, my name would be “McPivot.”

Bag of Tricks

OK, once you have a reasonable level of comfort with the workings of IF(VALUES()), whether that comfort is “I understand it” or even just “I can make it give me the right results,” you are in for some treats.  There are a number of ways to use IF(VALUES()) to produce different desired effects.  I’m going to blast through a few of them here real quick.

Note:  If you have not read the previous post, you should do so before reading this one.

Returning BLANK() for Subtotals and Grand Totals

Sometimes you end up with a measure that simply doesn’t make sense unless you are in the context of a single item.  Returning to the football project, let’s say I write a measure that for “Personal Best Single Game Rushing Yards,” which, for a given football player, returns their personal best single-game total over all time:

Best Single Week Performance Measure in PowerPivot

Cool huh?  (My data ends before Adrian Peterson broke Jamal Lewis’ record).  By the way, the formula for that measure is:

   =MAXX(ALL(DimProschedule[GameDate]),[Rushing Yards])

(To understand how that measure works, I refer you to one of my personal favorite posts, the Five-Point-Palm, Exploding Function Technique.)

Now, if you put another field on the pivot, you have a decision to make:  do you want that measure to be evaluated at levels other than a single player?  How about for an entire team?  Or all players of a given position?  You may decide that doesn’t make sense, and prefer that the measure NEVER return a value in those cases.

Good thing we have IF(VALUES()).  We can create another measure based on the existing measure:

   =IF(COUNTROWS(VALUES( CleanPlayers[PlayerID] ))=1,
       [Best Single Gm Rush Yds], BLANK())

Best Single Week Performance But Player Only Measure in PowerPivot

Notice how the original measure does not reflect the best player total?  In the week that Lewis ran for 295, apparently the Ravens had other players run for a total of 39 additional yards.  Depending on the semantics desired, you could argue that the Ravens’ number should be 295, for best single player performance in a game.

Or you can just return blank and forget about it, as my second measure does.

Returning different measures for different cases (aka “Branching”)

If we look at the picture above, you may say “hey it’s not fair to compare QB’s against RB’s in rushing yards, shouldn’t each position be measured by its primary metric – pass yards for QB, rush yards for RB, and receiving yards for WR’s and TE’s?”

Yeah, let’s do that.  First let’s create similar “best game ever” measures for the other two kinds of yardage – passing (throwing) and receiving (catching).  (Rushing is running, btw).

Best Single Week Performance All Three Measures in PowerPivot

OK, cool, so we have all three measures, but it results in a scraggly pivot.  I want a single column of numbers, and for the number to reflect the position (primary role) of the particular player.

IF(VALUES()) to the rescue.  A new measure that “branches” into the right measure for each position:

   =IF(COUNTROWS(VALUES( CleanPlayers[PlayerID] ))=1,
      IF(VALUES(CleanPlayers[PositionCode])=”QB”, [Best Single Game Pass Yards],
      IF(VALUES(CleanPlayers[PositionCode])=”RB”, [Best Single Game Rush Yards],
      IF(VALUES(CleanPlayers[PositionCode])=”WR”, [Best Single Game Rec Yards],
      IF(VALUES(CleanPlayers[PositionCode])=”TE”, [Best Single Game Rec Yards],

Best Single Week Performance Branching Measure in PowerPivot

Cool, huh?  A few things to note:

  1. IF(VALUES()) is used to check the value of [PositionCode], but [PositionCode] isn’t even on the pivot!  This works because each PlayerID does correspond, in the CleanPlayers table, to a single position code, because each PlayerID is unique.
  2. That said, it IS hard to tell which player is which position, especially for the 49ers, who had awful teams during the years for which I have data.  So I’ll add Position to the pivot below.
  3. I’m pretty sure that Johnnie Morton had his 153 yard receiving day when he played for the Lions, not the 49ers, but solving that problem is best saved for another day.

Here’s the pivot with position added in for clarity:

Best Single Week Performance Branching Measure in PowerPivot 2

(I decided to screenshot the Bears section rather than 49ers because the Bears had better players in those years.)

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 3 Comments
  1. I actually saw McLovin himself in Brooklyn this Saturday. Almost died. Wanted to ask for his autograph but couldn’t remember his real name. Branching seems very useful–looking forward to testing it!

  2. I have big data set of sales table and also I have so call dimension tabel. In my dimension table, waht I have is column1categoryName column2 coditionRate column3 conditionRateType. In column3 conditionRateType I got % and $ sign and in the column2 conditionRate “Percentage value for % and absulate value for $”.Now I want to calculate the commission based on my conditionRateType like if my conditionRateType is =% then calculate sales * conditionRate otherwise show me absulate value. When I use if function to solve this problem it is throwing me error message. Appreciate your help to solve this problem.

Leave a Reply

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