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

“Unless I am wrong, and I am never wrong, they are headed dead into the Fire Swamp.”

-Prince Humperdink

A funny thing happened today.  Kasper de Jonge asked a couple of questions (in comments) about the Introduction to DAX Measures post.  I answered the “easy” question, and then went off to find the answer to the difficult questions.

Heh heh, I had them backwards.  The “easy” question led to an in-depth discussion with Howie and Marius from the DAX team, during which I discovered an error in the “Qty per Day” measure.

I’ve uploaded fixed versions of the videos, so anyone watching for the first time (or re-watching now) will get the proper information.

And the workbook file available here is also fixed.

If you watched the vids already and don’t want to watch them again, here’s a quick summary of what I changed:

  1. Remember the golden rule, “Filter then Calculate?” 
  2. Filter only impacts the “home” table (aka Fact table).  So far so good.
  3. But the filtered home table is never then used to then filter other tables during the Calculate phase.  Not even if you use RELATEDTABLE().  So for example:
    1. In my former example, I used COUNTROWS(RELATEDTABLE(DimDate)) as the denominator of the measure
    2. That expression, however, was returning the grand total number of rows in DimDate, no matter how the pivot was sliced.  (Because of point 2 above)
  4. The fix was to use the [OrderDateKey] column from the FactInternetSales table (the home table) and do a COUNTROWS of the DISTINCT set of values in that column.
  5. Note that if there had been a slicer (or row/column field) from the DimDate table in the pivot, then the DimDate table would have been filtered, but:
    1. Not as a result of the Fact table being filtered
    2. It is strictly filtered only by fields in the pivot that came from the DimDate table
    3. And it is therefore much less filtered than the Fact table, which is filtered by all of the fields in the pivot
  6. Along the way I ran into the fact that the DISTINCT function requires an explicit reference to TableName[ColumnName], and does not accept the shorter [ColumnName] only syntax.

…yeah, the revised videos might still be worth a look 🙂

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 4 Comments
  1. Really useful stuff, Rob, thanks very much.

    In the spirit of your example I’ve made a PowerPivot-based chart that slices a set of values into groups, like so:

    1) 0 < value < 200

    2) 200 < value < 400

    3) 400 < value < 600

    I can make a count of values appear as totals underneath the sliced column of values, and the count does refresh when I use the slicer.

    And I can reflect that count to a more convenient location next to the slicer.

    But it looks like there's some event wiring that needs to happen in order to make the reflected count respond to the slicers?

  2. To be a bit clearer on the strategy, I’ve added a PowerPivot measure defined as


    This produces a column of ones with a total under it.

    Then in Excel-land I’ve got:


    Which captures the changing total. But the formula doesn’t recalc when the slicer changes the total, it has to be manually refreshed.

  3. Follow-up: It was only, as you guessed, that recalc had been set to manual. I’m not sure how/why.

    I am not (clearly) an Excel guru. But in way that’s exactly the point. In an era of widespread access to open public data, a whole lot of folks who are not Excel gurus will find themselves inspired to try stuff with PowerPivot.

    Thanks Rob!

Leave a Comment or Question