California Power Pivot / Power BI Pro for Hire

August 31, 2015

Post by Rob Collie

Solid Analyst / Data Modeler / Dashboard Dev Available Immediately

A few months ago, I convinced one of my younger friends to take a big risk.  Proficient in DAX and M, the heart and soul of both Power Pivot AND Power BI, he was ready for bigger things.  His role at his employer in Ohio didn’t acknowledge or encourage his professional growth.

With my encouragement, he accepted an aggressive new job in California  He packed up his life (as did his wife!) and they were off to the west coast.

Well the new role turned out to be more “BI Director” than “Place to Crunch Numbers as Part of a Team.”  The kind of job where the mechanical/analytical skills are only one small part of the puzzle, the mission is completely undefined, and even the underlying business itself is in a fledgling state.

So I had talked him into taking a job, TODAY, that he SHOULD have been accepting in five years.  I pushed him too hard too fast, and it’s not working out.  He needs a different job, one where he can ply his craft, contribute solid value, and learn more about business.

His salary needs are modest, his skills developed, and his attitude positive. If your company needs help, he’s worth a serious look.  If you’re interested, please reach out at the email address below and we’ll get you more information.

Find Out More

Grab a good team member who will help your org transition to Power BI.

Excel to Power BI Webinar Recording and a Call for Your Votes!

August 28, 2015

By Avi Singh [Twitter]

Friends, it was a joy again to host our free monthly webinar Excel to Power BI. As usual, we spent more than an hour on a lengthy Q&A session after the presentation. You can find the the recording at the end of this post.

Call for Your Power BI Votes!

In the webinar, we spoke about how there are two key scenarios for Excel Pros which are broken in Power BI.

1. Importing PowerPivot Data Model into Power BI Desktop

2. Connecting Excel to a Power BI Model

Now you can get a sense of how fast the Power BI team is working, since our webinar was on Aug 18th and on Aug 20th they announced that the latest Power BI Desktop update now supports #1 Smile

That aside, Power BI team is certainly listening to the users. But, Excel Pros need to step up and speak up to get their voices heard. The means to do that is by submitting and voting for Excel friendly Power BI ideas on their support site.

Ones that we would recommend voting are compiled at

Since we submitted the “Connect Excel to Power BI Data Model…” idea (#2 above) it has been steadily climbing the charts. And is currently #1 under Hot Ideas on Power BI. That’s great, but keep your votes coming so that the Power BI team appropriately prioritizes this request.

Keep Voting!

Enjoy the webinar recording using links below.

Excel to Power BI: Webinar Recording Aug 18

Click above or Use the links below to jump to a specific topic:-
00:28 Start of Webinar
01:26 What is Power BI
03:17 Why use Power BI
08:34 How to use Power BI
12:55 Demo – Start
13:32 Demo – Get Data
16:31 Demo – Create Reports
17:22 Demo – Create Dashboards
20:23 Demo – Share Dashboard
27:37 Demo – Mobile App
29:17 Demo – Refresh
31:06 What’s Next

Power BI Desktop Now Converts Power Pivot Workbooks

August 25, 2015

Post by Rob Collie


Another Excellent Step:  Power BI Desktop Now Converts Excel Power Pivot Workbooks

Download the Latest Version of Desktop and Try It

I know, I know.  I’ve been talking about Power BI Desktop a lot lately. But to be fair, I had written NOTHING about it until very recently, so there was a backlog of sorts to be cleared.  I feel like that “behind-ness” has now been adequately addressed by posts here and here, with additional posts here and here by Avi and Andrew, respectively.

But now, they’ve added something significant that you likely find quite interesting:  you can take your existing Power Pivot workbooks and convert them to Power BI Desktop (PBIX) files.

Download the Latest Power BI Desktop

Brings in Data Sources, DAX, Relationships, Queries, Power View…

Read the rest of this entry »

Quick Poll on Data Nerd “Art”

August 21, 2015

Post by Rob Collie

No, We’re Not Opening a Store…

Not any time soon anyway.  if we do any “merch” stuff near-term, it would be as SPECIAL GIFTS.

But we’d want such stuff to be, ya, know, desirable just the same.

Click to take the 5-second poll!

Which of these (if any) make you smile?  Click Image and pick 1-2, please Smile

Click Image Above to Tell us Your Favorite(s)!

Power BI Desktop New Feature: Bi-Directional Relationships!

August 20, 2015

Guest Post by Andrew Todd

Intro by Avi: We’ve been excited about all the new functionality that Power BI Desktop brings to the table. Bi-Directional Relationships is one of those new features.

Before this (and still for the users of Excel+PowerPivot), you could force a relationship filter to flow “uphill” and implement many-to-many relationships. However that was done inside your DAX measures (click to read a detailed how-to). Now, with Power BI Desktop, that can be done automatically via bi-directional relationships. Andrew shows us how…

Bi-Directional Relationships make our relationship filter flow “up” and then “down” when user clicks to filter in this Power BI dashboard

Note: Download the example .pbix at the bottom of this this post, which includes the above dashboard, example bi-directional relationships setup and showcasing other new features in Power BI Desktop

Lookup tables in PowerPivot are like reservoirs holding torrents of instructions poised to break free at the click of a slicer tile. When a user clicks on a slicer connected to a lookup table, they open a flood gate and instructions are unleashed to flow downhill to data tables.

In Power BI Desktop, filters can defy gravity! Not only can filters flow downhill from the reservoir into the data tables… they can also flow uphill from data tables to lookup tables! Those instructions flowing uphill into the lookup tables can then spill over to data tables on the other side of the lookup table!

Driving Sales Activity Metrics from the Back Seat

With filters flowing uphill, the filters from the data table side of the relationship can be sent back to the lookup table. In a sense – those filters can actually flow right through the lookup table and down to data tables on the other side! The lookup table itself is filtered and the context ‘splashes’ over to the other data tables.

Power Pivot, PowerPivot, Power BI Desktop

Read the rest of this entry »

Grab Bag: Job Opps, Sold Out in Indy, and a Vibrant Power BI Discussion

August 18, 2015

Post by Rob Collie

Power BI Job Opps, Power Pivot Class Sold Out, Power BI Discussion

Four quick topics today:

  1. Mini-application for for PowerPivotPro jobs
  2. Specific job opp in Seattle area
  3. Indy class sold out
  4. Last week’s Power BI post and the (even more interesting) followup discussion

1) “Mini-Application” Form for PowerPivotPro Jobs

Back in January I mentioned that we’d be expanding the PowerPivotPro team this year.  The calendar tends to zip by under our feet rather quickly, and here we are in August, but we’ve quietly been growing the team, slowly, in the intervening months.

Now however it’s time to lay the groundwork for the next growth phase.  Microsoft is getting serious about Power BI, which is right in our wheelhouse (since it’s built on the Power Pivot and Power Query engines, and philosophically is very much “of our religion.”)  So we’ve got to be ready for the next surge in client demand.

Read the rest of this entry »

Free Webinar: Excel to Power BI, Tue Aug 18

August 14, 2015

By Avi Singh [Twitter]

We’ll be hosting our monthly “Excel to Power BI” Webinar on Aug 18. Short notice, I know. But that means you have no excuse to dally, register today! Invite your colleagues, friends, even your mum. The webinar is really one of the most fun things I do. I always love talking about Power BI, PLUS I get to interact with all of you in the Q&A session (the Q&A session has gone on for an hour or more in our past webinars).

Click buttons below to find out more or to register.

Free Webinar
Tue Aug 18

PowerPivotPro Free Webinar Series

If you would like to stay notified of our future events, please subscribe to us via email.

Building Datazen Dashboards from Existing Power Pivot Models

August 12, 2015

Guest Post by Andrew Todd

Build awesome dashboards for mobile and desktop using Datazen

Datazen is a great visualization tool that is free to Microsoft SQL Server Enterprise customers. If you haven’t worked with Datazen yet, check out this post by Rob and Chris! Datazen allows you to design an awesome dashboard before you even work with any data! You simply draw a visualization, and then Datazen reverse engineers the exact layout of the aggregate table that you need in order to make that visualization work.

If you already have a PowerPivot model that you use for reporting, naturally you’ll want to use it to build visualizations in Datazen rather than reinvent the wheel entirely. One way to incorporate your dozens or even hundreds of measures and model logic (not to mention dozens of hours) into Datazen visualizations is through DAX queries.

Two Paths to Datazen DAX Query Nirvana…

Ok, you’ve built a fantastic BI dashboard in Datazen and your chakras are perfectly aligned. Now, there are two enlightening paths that you could take to build tables for Datazen visualizations with DAX queries:

A) Create a new ‘Data Connection’ in Datazen to your PowerPivot model hosted on SharePoint/SSAS Tabular, using the Analysis Services Data Provider. You can automate the refresh of data with SharePoint/Power Update and in Datazen itself.

B) Query your Power Pivot model using DAX Studio and save the workbook to a One Drive folder, then import the data into Datazen. This process can also be automated using Power Update.

Let’s take a closer look at both of these methods, starting with a SharePoint hosted Power Pivot model.

Read the rest of this entry »

Transitioning from Excel Power Pivot/Query to Power BI Desktop: A Remarkably Smooth Road

August 11, 2015

Post by Rob Collie

Transitioning from Power Pivot in Excel to Power BI

Can Excel Pros Make the Jump?

For 5+ years, Excel was the ONLY “place” where we could get our hands on the awesome power of the DAX engine (aka Power Pivot) and the M engine (aka Power Query).

It wasn’t until recently that we were given ANOTHER place, in the form of Power BI Desktop, where we could use those incredible analytical engines. 

And even today, Excel remains REMAINS the best STARTING place for the world’s tens of millions of Excel Pros (who I have long defined as “anyone who uses PivotTables, VLOOKUPS, and/or SUMIF multiple times per week.”)  We’ve seen this over and over again, both with other tools like Tableau and now with the new Power BI:  these Excel folks are quite wary of new tools, leading to widespread poor adoption.

So, given that there’s a sizeable number of humans using the Excel version of these tools, and that the Excel version is by far the best “on ramp” to the new languages of DAX and M, it’s a natural question…

…how hard is it to pick up Power BI Desktop if you are already competent with Power Pivot?  (and perhaps also with Power Query?)

But First…  WHY Make the Jump at All?  SHOULD We?

Don’t EVER tell me that I HAVE to use a new tool.  That equals “huge disruption to my workflow” in my mind and I don’t have time for such nonsense.  Instead, let me discover that I WANT to use that new tool, to IMPROVE and extend my EXISTING workflow.  -any Excel Pro

I am 100% sympathetic to the Excel Pro stance of resisting new tools. Ich bin ein Excel Pro myself, as I’ve said before, and in the early days of Power BI Desktop’s existence, I kept it at arm’s length.

Read the rest of this entry »

Nested SUMX or DAX Query?

August 6, 2015

by Matt Allington

I was helping a friend out recently with an interesting problem.  It all started with a SUM( ) that wasn’t behaving. It quickly became a SUMX( ) problem but evolved into a DAX Studio/Query problem.   Let me explain.

The Root Problem – Same Store Sales

Many retail businesses open and close stores throughout any given year.  This creates a problem because it is difficult to determine if business growth is vertical (increased sales within existing stores) or horizontal (expansion of the store base).  It is common to do analysis on “same store sales” where you only include stores that had sales for the entire period this year and also last year. There are quite a few posts already on this topic on, but this is a good opportunity for me to talk about SUMX and also DAX as a query language.  I have reconstructed the scenario with some test data.  You can download the workbook here if you want to take a look.

I started off with some base measures as follows:

A. Total Sales:=SUM(Sales[Extended Amount])
B. Total Sales LY:=CALCULATE([Total Sales], 

There are a few different ways to work out sales last year, but I have used SAMEPERIODLASTYEAR above and this works just fine in this instance.
Read the rest of this entry »

So Your Detailed/Flat Pivot is Slow and Doesn’t Sort Properly? Try Text Measures!

August 4, 2015

Post by Rob Collie

Detailed Pivot Report Using Flattened Pivot

Does Your Pivot Look Like This?  Does its Slow make you Sad?  Time for a Fix!

Tell me if this sounds familiar…

Yes, you know that pivots are meant to show aggregations.  Summaries.  Pivots were NOT invented to display thousands of rows of detail data.

But still, sometimes you need to do precisely that. The biz needs its list of customers and how much they’ve been buying, for instance, and all that data is in YOUR Power Pivot model.

And hey, pivots are really the only game in town* for table-shaped display of data.  So, you build one of the monstrosities like the above.

(*OK yeah, you DO know about this thing called DAX Query Tables, but those are seriously a pain to set up.  So, no.  You rule those out before even starting.  Just like me!

So You Do The Flattened Pivot Dance, Right???

In pictorial form…

Detailed Pivot Report Using Flattened Pivot

The Flattened Pivot Option – Found in the PivotTable Dropdown in the Power Pivot Window

Detailed Pivot Report Using Flattened Pivot

Next, You Pile a Whole Bunch of Fields Onto Rows

Turn Off Subtotals In Your Flattened Pivot

Then, on the PivotTable Design Ribbon Tab, You Turn Off All Subtotals

And Voila!  It’s Slow as Heck.

Read the rest of this entry »

Power BI Desktop (Designer) vs. Excel: Rematch

July 30, 2015

By Avi Singh [Twitter]

In our last webinar the #1 question and confusion seemed to be around Excel versus Power BI Desktop (formerly called Power BI Designer). The thing is, both tools are really part of an ecosystem. And the biggest confusion was around understanding how these tools fit into the overall landscape.

Here is the picture we would build to, but do read through so you understand how we arrived here and also find out how you control the future direction of Power BI (seriously, you do!).

Excel versus Power BI Desktop
Note: Things change fast in the Power BI world, so this picture may change as well

Standalone Tool for End-to-End BI

Rob did an excellent head to head comparison of Power BI Designer versus Excel. Just read that, but I’ll add some commentary.

If you were to use a single tool for BI – getting data, building a data model and building charts/reports – we have firmly established that Excel is the world’s best data tool, period Smileand that is primarily due to it’s internal network effect and that it is easy to adopt.

Excel is the World’s Best Data Tool, Period

Power BI Desktop is a very slick tool, with all different facets meshed together beautifully without any seams showing. Unlike Excel, where due to the “Add-In” nature Power* tools always feel a little clunky, not to mention cases where they go totally awry. But in spite of all that, it is hard, no impossible, to build a tool that can supersede Excel, or replace Excel.

However that is not quite a fair comparison. Because Power BI Desktop is primarily built for

Power BI Desktop for

Power BI Desktop is primarily an accompaniment to It is essentially an “authoring tool” for Its true purpose is to build something that gets published to

The last step in Power BI Desktop is to publish it to

You would almost never use Power BI Desktop standalone. Well you may, but understand that it is not really built for that purpose.

With these two combined – Power BI Desktop and – they outshine Excel in the BI department. With the fluid development environment of Power BI desktop for authors…and the rich, easy to share, mobile ready visualization platform of

However that is not a fair comparison either, since Excel models can be published as well. Read the rest of this entry »