“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


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!


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):
    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