Just around the corner: May 4-5 Online Power Pivot Class

April 27, 2015

Friends, the goal of our Instructor-Led Online class is to reach the folks, we are unable to reach via our other training options. I am happy to say that attendees for the past classes have spanned the globe. Our next class is just around the corner on May 4-5 (Mon/Tue 9-11AM PST). If you would like to jumpstart your Power Pivot and Power BI journey from the comfort of your own office or home, go ahead and sign up.

Class Attendees span the Globe!

General feedback has been positive, with a 4.3 out of 5 Course Rating.

Here is what one attendee had to say

“The class was great! Just the amount of time so as not to interfere with our hectic work schedules, and the hands on homework really helps drive understanding. The instructor was also fantastic and did a great job pausing for questions and explaining the material in layman’s terms.”

Do note that this is a basics course targeted towards beginners. Do evaluate all the learning options and choose one that best fits your needs and learning style. A unique feature of the Instructor-Led Online class is that you are invited to a Q&A session each month, where you can bring in any of your questions.

“You are invited to Q&A Sessions each month to help you along your Power BI Journey”

The idea is to to offer continued help, as you progress on your Power Pivot/Power BI journey after the class.

Power On!
Avi Singh

VLOOKUP better than Relationships? Hell must have frozen over

April 23, 2015

By Dany Hoter

We always think about a data model with multiple tables and relationships between them as a big improvement over the common practice of combining tables using VLOOKUP expressions.
(Watch Video for more: PowerPivot Relationships are EASIER than VLOOKUP, not just faster)

I came across a customer request that forced me to admit that in some aspects a VLOOKUP is more flexible than relationships. To solve some specific scenarios we have to use modeling methods which are not elegant and not efficient.

VLOOKUP better than Relationships?

I hope that some of the readers will know to defend the cause of the data model and find a magic solution that is both elegant and efficient.

How can good old VLOOKUP be better?

A solution based on VLOOKUP is using a separate formula for every row, this is what makes it cumbersome, slow and error prone – right?

Yes but this is also what makes it more flexible in some cases.

Let’s describe some reasons why is it more flexible and give some examples:

VLOOKUP can use a different way to find the right row based on the input value

, VLOOKUP (A6,lookuptable6,3,false)
, VLOOKUP (A5,lookuptable5,4,false))

If lookup value is empty in our table, there is no point in looking for it in the lookup table and instead a different lookup table is used and a different lookup value is searched for.

For example (download), if a state column is empty or contains “NA”, return a value from the countries table using the country as a search value.

VLOOKUP gives us the flexibility to lookup State or Country names Read the rest of this entry »

In-Person 2-Day Class, May 12-13, Cleveland

April 17, 2015

Post by Rob Collie

2-Day In-Person Power Pivot Class

Hey folks, just a quick reminder that I’m teaching a class next month here in Cleveland.

(Someone registered today from New Mexico!  If they can make that trip, anyone can.)

Optional Gathering/Dinner on “Middle” Night


Some Folks from the August Class Throws the “Excel Gang Sign” at Dinner
(Can You Spot MrExcel Himself, Bill Jelen?)

On the night of the first classroom day, we’ve been running an informal gathering at a local restaurant, and many people reported that it was one of their favorite social outings ever.  Just being surrounded by people with similar backgrounds, mindsets, and problems is a unique experience, especially for the Excel crowd.

Totally optional, but we’ll be doing this at all classes.

Included:  Thumb Drive and 50% Off Online University

All Students Receive a PowerPivotPro thumbdrive containing the materials from the course:


And also a 50% off discount code to the online video course.  Past students have found it to be a helpful “reinforcement” to the live class (as well as covering topics that we don’t have time for in two days), so we have decided to offer it as a bundle.

May is actually a great time to visit Cleveland too.  Hope to see you there.

Click Here for More Info and Registration

Another Previous Cleveland Class

Jump between pivots with context

April 16, 2015

by Matt Allington Today I am sharing a trick that I have used a number of times for clients – it allows you to use simple VBA to jump from one pivot table to another, and when you arrive at the second pivot table it is automatically filtered to show the context for the data you want to see.  It is a great user experience.

Here is a sample use case and demo

You are browsing and drilling into a product hierarchy looking at the change in performance vs last year.  You drill down to the product level and want to see the weekly sales for that particular product.  You simply click on the “show detail” button and you are taken to a detail page that shows you the details for the specific product you were looking at.  jump with context   Let me share the process of how to build this interactive report.

Use the VBA Recorder to do the heavy lifting

After I set up my 2 Pivot Tables, I recorded a couple of steps with the VBA Recorder to help me write the code.  Here is the process I followed. Read the rest of this entry »

Is Your DAX/PowerPivot/Power BI Computer FAST?

April 14, 2015

Post by Rob Collie

Best and Fastest Computers for Power Pivot, DAX, and Power BI - WE WANT TO KNOW!

Looking for the Fastest Desktops, Laptops, Tablets, and Servers!

“What’s a good computer for running Power Pivot?”  That’s a question we get asked all the time.  And we do have lots of advice – things that make a good CPU, RAM considerations, etc. – but that always translates to a hunting license of sorts. 

It would be much nicer for us to say, “here are three models in your price range that we recommend.”

And to be comfortable doing that, we need to specifically test for speed.

So if you have five minutes, please take a moment and run a quick test.  We provide a benchmark workbook and instructions.

The Links!

Excel 2010 Benchmark Workbook

Excel 2013 Benchmark Workbook

Submit Your Results Here

The Results!

We will leave this open for a week or so, then summarize the results back here on the blog, along with some very specific recommendations on particular hardware models in various price ranges and form factors.

“Beta Test” the New Reference Card?

April 7, 2015

Post by Rob Collie

Power Pivot / Power BI / DAX Reference Card

RefCard Pt 2: Captures/Condenses Important Tips & Fundamentals from Our Years of Teaching
(Five “Sides” Pictured Above, Sixth “Side” is Still in Development)

PDF Preview Available Later This Month

Short Version:  drop us an email at the address below, and later this month we will send you a preview of the new reference card in PDF format.

Drop Us a Note to Get Your Preview Copy

“Completing” the First Ref Card

Creating the first reference card might be the smartest thing we’ve ever done. 

We can say that without hubris, because it was actually NOT our idea, it was Bill Jelen’s.

Still though, we have the wisdom to recognize Bill’s genius.  The reference card has been a CRITICAL component of our classroom teaching for years now:

Power Pivot / Power BI / DAX Reference Card

Ref Card Part 1 (Laminated Version) is Only $2.95 at MrExcel.com – Click Image
(Preview Participants will Also Receive a PDF of Part 1)

Final “Product” is TBD

1) Given that the new reference card is six sides, it ideally would be printed in some sort of “tri-fold” laminated format, and in full color of course.  That’s might be pretty expensive to produce, so we’re still noodling on how to do it.

2) Plus, it would make sense to merge it with Part 1 to form something more like a quad-fold.  Hmm.

3) We want your feedback on Part 2 so we can improve it before we do anything rash like print 5 thousand copies.

4) I’m also contractually obligated to say that we WILL improve the aesthetics of Part 2 as well.  Because, well, *I* am designing Part 2, and while I might be a decent communicator, I’m not exactly a graphic designer.

Anyway, Sign Up for the Free Preview PDF Smile

Drop Us a Note to Get Your Preview Copy

Protecting time intelligence expressions against non-consecutive range of days

April 2, 2015

By Dany Hoter

(Download file here to follow along).

Time intelligence functions are some of the most important functions in DAX.

Being able to compare values between current period and the same period last year is a very common request and one that is a real challenge using native Excel and can easily achieved with DAX.

A typical calculated field might look like:

Sales Last Year:=CALCULATE(

In quite a few cases once you start using such expressions you will see this annoying error message:


The reason is that the time intelligence functions like the one used here SAMEPERIODLASTYEAR require 100% completely consecutive dates in the filter context, with no holes. Read the rest of this entry »

The Diabolical Genius of “SWITCH TRUE”

March 31, 2015

Post by Rob Collie

SWITCH TRUE Alternative to Nested IF's

Did Someone Say Deliberately “Misuse” a DAX Function for Our Benefit?  We’re IN!

An End to Nested IF’s?  Sign Us Up!

When we first saw the SWITCH function make its debut in Power Pivot a few years back, it was a “hallelujah” moment.

Whereas we used to have to write nested IF’s, such as this:


Now , with SWITCH, we could write that much more cleanly as:


Which do you prefer?  It’s easy to make a strong “case for SWITCH,” isn’t it?

But What About Cases Other than Equals?

Now, let’s consider the following nested IF:


Notice that we’ve swapped out “=” for “<”.

And we can’t do that as a SWITCH, because SWITCH checks for exact matches between [Measure1] and 1 (or 2, 3, etc.)

This is unfortunate, because in these cases, we’ve had to keep using nested IF’s.  And wow do I (Rob) *hate* nested IF’s.  I can never seem to match the parentheses up correctly on the first try.

But There’s a Sneaky Antidote!  We CAN Still Use SWITCH!

Read the rest of this entry »

Best way to learn Power Pivot: get over analysis paralysis

March 30, 2015

I have loved reading ever since I was a young kid. In my adult life though, reading fell on the wayside, as life got busy. Now, I have worked as a Business Analyst for a long time, and the role suited me naturally. However at times I fall victim to analysis paralysis. My inability to get my reading fix, was one casualty. Let me explain.

I felt that since I was soooo busy, I had to make the best use of my time. I wanted to pick the BEST business books, the BEST self-help books, the BEST fiction books that would enrich me. Unfortunately that meant, I would occasionally go on the hunt for the “best”, give up after a while, and repeat the cycle; without ever actually reading many books.

It was years before I realized my folly. The way out was the “Librarian’s Picks” section at our local library. I would trust their judgment, and started picking several books from that section. I did more reading in that one year than I had done in the previous ten years.

If you are anything like me, you have thought about attending Power Pivot training, but have vacillated for this long while. Let me say, a Live Instructor-Led class is the most intensive way to jumpstart your Power Pivot journey. There is still time to enroll in our class on Apr 1-2 in Seattle.

Live In-Person Class
Apr 1-2

PowerPivot for Excel Live Class (Seattle)


Power On!
Avi Singh

Power Pivot and Basketball Superstars: Many-to-Many and USERELATIONSHIP

March 27, 2015

By Avi Singh [Twitter]

Friends at a company play pick-up basketball during their lunch hour. Since there are no established teams, players can be randomly matched up. But these folks happen to be engineers/data-nerds, so they keep detailed track of games, teams, players and win/loss. The diagram view of the data is shown further below.

Question: How can we determine which player pairing is the most successful?
Since players are randomly teamed up, are there combinations which when teamed up have an unusually high winning percentage?

Word Cloud of Player Nicknames: Size of text indicates number of games played

Application: This would naturally extend to other sports, but I believe may also apply in many non-sports scenarios, where items are paired up somewhat randomly (or by design) and we want to know how effective those pairings are.

Thanks to Kirill Perian (basketball nickname K-Real), an attendee of one of our past webinars, who sent us the dataset and posed this question. Dataset has been simplified to showcase this scenario and anonymized to protect the identity of the losers :-)

File can be downloaded here.

Model Diagram: Showing Game, Team and Team Players

First we will address the easier scenario of creating metrics for individual players, using the many-to-many pattern. Next we will take on writing measures to compare performances of pairs of players.

Read the rest of this entry »

Power Update Adds Email Notifications, Macro Support, and 100% Free Version

March 23, 2015

Post by Rob Collie

Power Update Now Emails PDFs or Full Power Pivot / Power BI Workbooks to Email Recipients

Autorefreshes Your Power Pivot Workbook as Frequently as Desired, Auto-Publishes to Any Location, and Now Sends Email Notifications of Success/Failures – With Attachments!

Our Gift to the Community:  New, Improved, and Free

The team has added several new features in the latest version of Power Update.  One of them (email notifications that optionally attach the updated workbook or PDF-ified version of the workook), is pictured above.

But the biggest new “feature” is that there’s now a 100% free version.  Go ahead and download it from the link below, and start using it today.

It will work forever – no trial expiration – and will never require payment.


(Alternate Location Here in case DropBox is blocked)

It will be installed and working in less than five minutes.  Have fun, and if you have any troubles, report those on the Power Update Forum.

“Why Free?  What’s the Catch?”

Simply put, we want everyone to have it.  Everyone.  It’s a game changer.  It will lead to more Power Pivot / Power BI adoption and overall goodness, which is very much something we want.

The only limitation in the free version is that it will only schedule one workbook. Every last feature is available – email, PDF attachments, publish to SharePoint and even SSAS Tabular.

So if you’ve only got one important workbook, you can use the free version forever.  A lot of people will run that way, and we’re ok with that.  If you someday end up with more than one workbook that needs refresh, you can opt to purchase the full version, which can schedule as many workbooks as you want.

“Wait, Can’t I Cheat That With Multiple Computers?”

Read the rest of this entry »

Course Prep: Excel 2010 and Excel 2013 PowerPivot Differences

March 20, 2015

By Avi Singh

Matt Allington, our friend from down under, had suggested the idea that we make some basic information available as course prep to students who enroll in our Live Instructor-Led classes (Upcoming: Seattle Apr 1-2, Online Apr 6-7, see Choosing your training option). Offering a course prep would enable us to get to the real fun stuff even quicker. I have taken that to heart and publishing some information in this post below and a Course Prep playlist on our YouTube channel. This is meant as a prep for students enrolled in our class, but may be helpful to others as well.

Course Prep Video Playlist (more videos to be added soon)

Key Differences between Excel 2013 and Excel 2010 User Interface

Launch Power Pivot
Create New Measure (Calculated Field)
Edit Measure (Calculated Field)
Insert a Pivot Table connected to Power Pivot Model
Add Excel Table to Power Pivot
Power Pivot Home Tab
Power Pivot Existing Connections

Launch Power Pivot

PowerPivot > Manage
PowerPivot > PowerPivot Window

Read the rest of this entry »