The Incredible “Iffer-Blanker-Filter” Measure

Omitting/Filtering Out Pivot Rows Based on an IF()

I’ve had this on my list to share for a long time, but David Churchward’s recent guest post bubbled it to the top.  At one point in that post, he had a nested IF measure that basically looked like this:

IF( <Non-Trivial Conditional Test>,
[Return an Existing Measure],
BLANK()
)

I find myself doing something like that once a week or so.  You just want the pivot to return certain rows – customers of a certain profit margin in David’s case, but it can be stores whose sales reflect certain characteristics, etc.

If it was just a static filter criteria, like “products that are blue,” then you wouldn’t need to put an IF in your measure at all.  You’d just set a filter in the pivot itself and use normal measures.

The key about these examples is that the filter criteria is dynamic – it takes into account selections made on slicers for instance.  And only measure logic can be dynamic like that.

But it’s Very Tedious to Write a Bunch of Measures Like That

OK, so you’ve written one measure that returns, say, [Sales] if the criteria is met, and BLANK() otherwise.  But your pivot needs to contain 6-7 different measures.  Do you want to go write that ugly IF measure another 6 times?  Doubtful.  And it would really suck if, sometime later, you realized that you needed to change the filter criteria.

David faced this problem in his post, and decided to write a single “flag” measure that returned 0 or 1 based on the conditional test, and then added a “Value” filter to the pivot based on that measure:

Pretty clever.  That way he doesn’t have to write the conditional test over and over in multiple new measures.  And given that it’s a “value” filter, it IS dynamic.

It caught my eye, because I’ve been doing this a different way.

My Approach – A “Flag” Measure that Returns 1 or BLANK()

I start out much the same way, with a single “flag” measure that evaluates the conditional test using an IF(), and that returns 1 if the condition is true.  But when it’s false, it returns BLANK() instead of 0:

[IfferBlanker]=
IF( <Non-Trivial Conditional Test>,
1,
BLANK()
)

Next, with my approach, I DO have to create multiple measures, which is a drawback.  However, each of these measures is very simple, like this:

[Filtered Sales]=
[Sales] * [IfferBlanker]

[Filtered Profit Margin]=
[Profit Margin] * [IfferBlanker]
…

Those resulting measures then return blanks if the condition isn’t met, and if every measure on a row returns blank, the pivot doesn’t show it (unless you change the default setting for blanks).  So it has the same end result as David’s approach.

Which Method is Superior?

I don’t think my way is better.  I plan to use David’s trick a lot actually – it’s a lot quicker to write one measure and apply a value filter than to write 6 or 7 measures, even if those measures are simple.  But I do think both methods have their usefulness.

For instance, if the report consumers are prone to fiddling, they may remove the value filter, perhaps even accidentally, and not realize that they are now seeing customers (or stores etc.) that do NOT fit the criteria.  And if the consumers of the model are  building their own pivots from scratch, they may not know how you applied the filter.  You may also sometimes want to mix and match filtered and unfiltered measures in a single pivot, which the value filter approach doesn’t support.  And if you’re going to build a LOT of pivots that require this filtering, investing in a handful of filtered measures may pay off in terms of effort, too.

I think I’m going to start using David’s approach about half the time, and stick with “Iffer-Blanker-Filter” measures for the other half, based on the situation.  Always good to have multiple tricks