skip to Main Content

Me and Rob were discussing a chapter from our new book: Chapter 25 – Time Intelligence with Custom Calendars: Greatest Formula in the World (GFITW). (This chapter received a complete overhaul by the way. There are many new/updated chapters in the 2nd Ed book).

image
Chapter 25: Time Intelligence and the Greatest Formula in the World!

I was saying that the GFITW pattern had wide applicability, even outside custom calendars, and was arguing that we should encourage our readers to read that chapter even if their work did not involve a custom calendar (e.g. a 4-5-4 week calendar, see sample or learn more).

Rob stopped me and asked me to provide an example. Put on the spot, I drew a blank. But I didn’t have to wait too long to run into my old friend: the GFITW pattern.

In the spirit of the Beauty Pageant Power BI Training Exercise, here is another one. And hint, the solution involves the GFITW pattern.

For this post, we won’t keep you waiting, and present the problem and the solution together. We do encourage you to take a shot at the problem before you review the solution.

<<DOWNLOAD Problem XLSX File>>

Problem

Note: We have simplified the scenario for the purposes of this training exercise.
Watch the video below or read on for an overview of the problem scenario.

PowerPivotPro ran a crowdfunding campaign, offering multiple perk levels, at which our supporters could contribute. These perks were cumulative as shown in the image below:

The offered perk levels were cumulative
The offered perk levels were cumulative

Thus if you ordered the Printed Book (#3), you would also receive DAX sticker (#2) and the e-book (#1). If you ordered the DAX T-shirt you would receive the poster, printed book, sticker and e-book.

Data

Contribution List: The contributor list downloaded from IndieGoGo looks as below:
Our Contribution List Table
Our Contribution List Table

Perk: We have also created a simple Excel table listing the perk levels.
image
Our Perk Table

Goal

Getting a count of all contributions by selected perk is easy. But what we really want is the measure below, which gives us a contribution count for “Selected OR HIGHER Perk”. This way we can determine, for example, how many e-books we truly need to deliver.
The DAX measure that we need to define
The DAX measure that we need to define

<<DOWNLOAD Problem XLSX File>>

Solution

Please take a fair shot at solving the problem yourself before taking a peek at the solution.

image 

<<DOWNLOAD Solution XLSX File>>

Here is the formula using the GFITW pattern (notice the similarity with the image at the top of this post):

ContributionCount_Selected Level:=COUNTROWS(ContributionList)

ContributionCount_Selected Level or HIGHER:=CALCULATE (
    [ContributionCount_Selected Level],
    FILTER (
        ALL ( Perk )
        , Perk[Perk#] >= MIN ( Perk[Perk#] )
    )
)

Greatest Formula in the World (GFITW)

Once I had set up the tables, my mind immediately went to the GFITW pattern.

image
Wash, Rinse, Repeat Smile GFITW goes everywhere…even beyond custom calendars

The GFITW pattern is often demonstrated in the custom calendar scenario. The basic workings of the pattern rely on a Numeric ID which identifies the custom calendar period.
image
GFITW on Custom Calendars requires a numeric PeriodID column

Then GFITW operates by

a) Clearing all filters

b) Then using some navigation arithmetic to select the needed period IDs for the calculation

The specific navigation arithmetic used depends on the measure you are attempting to write – Year-Over-Year, Year-to-Date, Prior Period, Rolling period etc.

In our case, we need to employ a similar strategy not on a custom calendar table but on our Perk table.

image
Our Perk# is similar to the Numeric ID column used for GFITW

If you would like to learn more about the inner workings and application off the GFITW pattern, you can grab a copy off our e-book today on MrExcel.com.

<<DOWNLOAD Solution XLSX File>>

WP_20151003_008BTW, I realized I messed up the caption in one of the beauty pageant photos that I posted. To clear that up, here’s my daughter again Smile She’s turning 7 in December.

Power On!
-Avi

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 7 Comments
  1. I’ve seen this pattern before in previous articles, it worked for what i needed at the time but I left in a haze of understanding what I did. Now, I just read thru Chapter 25, went thru the above problem, and arrived at the same solution. This time I, “got it” Thanks!!

  2. I’m getting there slowly. I have a problem where I need a solution. I believe my solution also lies in the GFITW as outlined in chapter 25, but I am not figuring this out. I’m trying to do a count of the number of tickets opened in the last week, by regional office. I want to post the problem on the forum, but for some reason says I’m locked out???? HELP!!!

  3. Hi Llewellyn,

    Avi sent me an email that you had this question. From the description here it reads like you were able to log in under a different name (question has a response in the forum). Please comment here if you are still not able to login.

  4. I have never fully understood what distinguishes the GFITW pattern from any other filter scenario in which you iterate through a single column of values and retain those that pass a criteria test. I mean isn’t that plainly what the DAX engine do all the time?

    I haven’t read your book yet though so there might be more to it than I initially realize.

    1. Hi Oxenskiold,

      You have an interesting point and I think I agree with your perspective. My take-away from this topic is that there are still many people that do not know how easy more advanced DAX can be: as soon as they become familiar with the GFITW pattern, how much more awesome stuff is in their reach.

      I think there are several benchmarks that Power Pivot users pass on their climb up the DAX learning curve, the GFITW being one of the earlier game-changers. Other benchmarks along the way might be discovering things like the two purposes of the FILTER function (a filter in one place and a table expression in another) or handling many-to-many relationships.

      To me, the most awesome thing is that, when prepared to meet the benchmarks, how easy it can be to have DAX power!

      I heartily recommend Rob and Avi’s new book, “Power Pivot and Power BI”, as well as Rob and Bill Jelen’s book “Power Pivot Alchemy” because of the benchmarks they brought me to and helped me pass with relative ease. However, the greatest tour guide for me to find benchmarks and avoid pitfalls was taking the Comprehensive Power Pivot Course and going through the practice workbooks until my personal light would go on.

      If you have not done so already, I invite you to sign up for the free powerpivotpro forums, which you can find here:

      http://forum.powerpivotpro.com/forums/

      I think you have much to contribute.

      Tom

  5. Hi Tom,

    Thank you for taking the time to answer my question.

    I guess you could say that PowerPivot(Pro)’s courses and online seminars must be battle tested by now. If experience shows that the GFITW pattern is a major benefitting factor in the learning process it couldn’t be better seen from my perspective. I still don’t quite get it though probably because I don’t have the experience from teaching dozens of people the particulars of DAX.

    I personally saw the light when I read Jeffrey Wang’s blog post ‘The Logic behind the Magic of DAX Cross Table Filtering’. I had to read the post several times though and experiment with DAX formulas to confirm my understanding. Then suddenly everything fell in to place and many-to-many relationships and many other details seemed to be not that difficult. Of course there were/are still details and intricacies to be learned, but that blog post did it for me. I just thought I would share my learning experience with you.

    Best regards.

    1. Oxenskiold,

      Ditto on Jeffrey Wang’s blog post. Each time I have read his blog post (like really sat down and read it), I have learnt something new. He’s the Einstein of my world. If you are reading and “getting” his blog then you’re far ahead of the crowd. “GFITW” is most useful as a pattern. Many folks out there either are not at the point where they understand the internals or just don’t have the time/energy to spare for that. Our main focus (mine at least) are Business User. I come from that background, I was a Business Analyst for a long time. And learning and applying something new while doing your day job is HARD. It’s like changing tires on a moving car. So we try to really simplify it for this set of users.

      I’ll say that I edge towards the more geekier side. Rob, kept me in check on the DAX 2nd Edition. He would constantly challenge me on the content or terminology I used, asking me “Would that make sense to the lay user”. He has more of a knack of simplifying things so they can be absorbed and applied by all users.

      You got me excited though, I need to find time and reread Jeffrey Wang’s article 🙂
      Power On!

Leave a Comment or Question