skip to Main Content

Replica of Shawshank Postcard

“Not long after the warden deprived us of his company, I got a postcard in the mail.
It was BLANK(), but the postmark said… Fort Hancock, Texas.”

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 Smile

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 5 Comments
  1. Hi, the picture of David’s flag measure doesn’t show for me. I’m particularly interested as I can’t get a measure into the filter section of the PowerPivot field list – do I have to have a hidden column?

  2. I can’t see it either :-(. I know it is a few years later, but if it could be added again that would be helpful.

  3. Here are my two cents. What I do is to create a column named “Show”. I use a formula that returns either TRUE or FALSE, and then filter only by that column on the table or on the pivot. for example =[customer_status]=active or =and([customer_status]=active,[customer_city]=mycity). or whatever conditions are required to show only the desired data.

Leave a Comment or Question