PowerPivotPro

PowerPivotPro is Coming to Boston

May 15 - 17, 2018

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MAY 15 - 16

Foundations: Power Pivot & Power BI

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work. Two Days in our class and you are EMPOWERED!

Overview:

  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
Boston Public Training Classes - PowerPivotPro
Boston Public Training Classes - PowerPivotPro

MAY 15 - 16

Level Up Series: Advanced DAX

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!

Overview:

  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.

MAY 17

Level Up Series: Power Query for Excel & Power BI

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!

Overview:

  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
Boston Public Training Classes - PowerPivotPro
PowerPivotPro Logo

 
image

 

“I like to carry it, you never know when you’re gonna need it.”

-The  much-missed John Candy as Uncle Buck

 

 

A technique that you may need someday

File this under “you may never need this, but when you do, you’ll know it immediately.”  I’d call this one a solid 4 on the DAX Spicy Scale.

Why I needed this:  I had two different calendar tables, one at the Date level and one that was a custom calendar, Periods table.  Most of the measures in this model are written to be used with one table or the other, and I never have to “cross the streams.”

But then I ran into a case where a measure I had written to be used with the Periods table, suddenly needed to also be used on a pivot that was only filtered by Dates.  And I didn’t want to write a new version of this measure (for reasons that are mostly irrelevant here).

Of course, when I put the Period-focused measure on a Date-focused pivot, and there were no fields from Periods on the pivot, well…  the Period-focused measure returned junk.

What I decided to do, then, was detect if Periods was the aptly-named “Sir Not Appearing in This Film” and then assign a Period in that case.  In other words, detect if the Periods table was not on the pivot, and if not, FORCE a Period value into the evaluation of my measure.

Detecting ALL(), or the Absence of Filter on a Field or Table

Here is the final measure formula I used, color-coded for identify its parts:

IF(COUNTROWS(Periods) = COUNTROWS(ALL(Periods))-1,
   CALCULATE(My Original Measure,
      FILTER(Periods, Periods[Period] = [LatestPeriod])
   ),
  
My Original Measure
)

Let’s go part-by-part:

The Detector

IF(COUNTROWS(Periods) = COUNTROWS(ALL(Periods))-1

The “detector” counts the rows of Periods in the current pivot context, and compares that to the number of rows in the Periods table with all filters removed by ALL().  Pretty straightforward right?

So…  why the –1 at the end?  The reasons for that are slightly academic…  academic enough that I don’t really want to understand in great depth.  Let’s go with  the short version:  in this case, when I counted the rows of ALL(Periods), ALL() was kind enough to include the “blank” row of the Periods table.

What’s that?  You say you don’t HAVE a blank row in your Periods table???  Well, neither do I.  But you MIGHT have some rows in your Sales table that have a blank value for the [PeriodID] column.  Or maybe you have rows in the Sales table that DO have a [PeriodID] value, but that value does not appear in your Periods table.  Either way, you implicitly DO have blanks in your Periods table, and COUNTROWS(ALL()) decides to tell you about it.  So you’ll subtract one and like it, soldier!

(I have not tested, honestly, whether you always need – 1 in this detector.  If your Sales table is perfectly clean, maybe the – 1 is not needed, and maybe it is.  Someone let me know OK?  And I bet three-to-one that the answer comes from Italy.)

The Original Measure

My Original Measure

OK, this part IS straightforward.  Whatever my original measure was named, or perhaps its full original formula, appears here.  Moving on…

The Filter for the “No Periods Selected” Case

FILTER(Periods, Periods[Period] = [LatestPeriod])

OK, in the case that Periods is absent from the pivot, I take the original measure and then use the FILTER function to pretend that the Periods[Period] column IS on the pivot, and filtered to a value matching the most recent period, as calculated by my [LatestPeriod] measure.

But really, this part is going to be VERY different based on the circumstance.  Maybe you want to use a completely different measure, for instance.  Or set Periods to the first period this month.  Or the period corresponding to the current filter context from the Date table.

I merely included the “meat” of what I did here to drive home the intent.

Two Notes

One – note that there is NO difference between “the Periods table is not used on the pivot at all” and “the Periods table IS on the pivot but unfiltered in the current context.”  So if you have Periods on a slicer but nothing is selected, the detector will “go off.”  And if you have Periods on rows, the detector will still “go off” in the grand total cell of the pivot (and maybe in certain subtotal cells as well).

Two – I was messing around in this area when I discovered the need for the long-simmering Precedence Project.  In other words, when you start messing around with overriding filter context like I did with the FILTER() function above, and you’ve got a number of tables and relationships in play, every now and then you see something you don’t expect.  For that reason, I plan to return to the Precedence Project shortly.

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 4 Comments

  1. Rob, it is hard to resist to such a request. 🙂
    If your data model is perfectly clean, COUNTROWS (ALL ()) returns the correct number of rows. If some of the rows in your fact table (the table which relates to the Periods through some column) contains some blanks, then it is as if a new dummy row is added to the Periods table to guarantee the correct behavior of relationships. The row does not exists, yet is is there.
    If your table has an ID not null, you can compute the correct number in this way (see below, this formula is far from being the best):
    CALCULATE (
    COUNTROWS (VALUES (Tab[Id])),
    ALL (Tab),
    NOT (ISBLANK (Tab[Id]))
    )
    This always returns the number of rows, excluding blanks. Nevertheless, there is the more convenient function COUNT, which automatically excludes blanks:
    COUNT (Tab[Id]).
    The only drawback is that you need to have a key but… hey, you have it, don’t you? 🙂
    All that said… well, maybe I’ll write a post about it, it looks an interesting question! 🙂

  2. Rob,
    I was just wondering if something like the following would have worked as the “detector” so that subtracting 1 would not be required and you wouldn’t need to differentiate the formula for where the Period mapping is perfectly clean.

    IF(COUNTROWS(Periods[Period]) = COUNTROWS(DISTINCT(ALL(Periods[Period])))

    (Note: DISTINCT does not return the blank value.)

Leave a Comment or Question