PowerPivotPro

The other day I was working on an updated version of my Team Playcalling report, which, by the way, look FABULOUS with Slicers:

Only problem with it was that it was taking 90 seconds to refresh.  Unacceptable.

Howie Dickerman and Marius Dumitru, two of the Superheroes of DAX at Microsoft, pointed out that my measures were using the FILTER function in places that were not necessary.

For instance:

  [New Measure] = [Original Measure] ( 
                      FILTER(DataTable[PlayType]=”Pass”)
                                              )

Can be rewritten without the FILTER function:

  [New Measure] = [Original Measure] ( 
                      DataTable[PlayType]=”Pass”
                                              )

When I switched over to using those expressions directly, rather than the FILTER function, my 90 second refresh time dropped to about 3 seconds.

That’s a pretty significant boost in DAX measure performance from a very simple change.

Why so much faster?

The explanation from Howie made a lot of sense.  When the PowerPivot engine is evaluating a measure, it already has to take filter context from the pivot itself – row fields, slicer selections, etc.

And since that’s pretty much the #1 use case for the engine, well, applying filter context to a measure is highly optimized and fast.

So it’s not a big deal for the engine to inject another filter like I have specified in the rewritten example – it gets treated much the same as if that filter came from the pivot, as if a slicer had been set to PlayType=”Pass”

But the FILTER function, on the other hand…  well, it creates a brand-new table in memory.  I know my example just filters by one column, but the FILTER function can do some pretty amazing things, dynamically responding to current context.

Because of that power, the FILTER function must either create or update that dynamic table for every cell in the pivot where FILTER is used.  I have 256 cells in this particular pivot (32 teams times 8 measures), and originally all of them used FILTER.  But given that some of my measures are based on other measures that aren’t displayed in the pivot, my actual cell count was even higher.

Now imagine what would happen in a pivot with 5,000 rows

Next Football Post:  Grading the Football Project  >>