The “Return Blank for Totals” Measure Written Three Different Ways – via HASONEVALUE, ISFILTERED, and HASONEFILTER.  So far, only HASONEVALUE seems to have a flaw.

Picking up from Tuesday’s post, which drew some quick comments, let’s add one more function to our evaluation:  HASONEFILTER().

Here are the three measure formulas from the pivot above.  They are all the same except for the highlighted function:

IF(HASONEVALUE(Stores[StoreName]), [Units Sold], BLANK())

IF(ISFILTERED(Stores[StoreName]),[Units Sold],BLANK())

IF(HASONEFILTER(Stores[StoreName]),[Units Sold],BLANK())

As covered on Tuesday, HASONEVALUE “fails” when you have a parent item (Region 0, in this case) that only has one child item (Store 26 in this case).

But ISFILTERED and HASONEFILTER do not have that problem, and they seem to be returning the exact same results.  Let’s “differentiate” them shall we?

### Bring in the Slicer!

Really, it’s not a “legitimate” post until a slicer shows up.  I should make this a rule or something:  never get involved in a land war in Asia, never get out of the boat, and always include a slicer.  (Who can name the two movies that are the sources of those first two rules?)

OK, now all three measures do different things, and only
HASONEFILTER continues to suppress subtotals.

Before the slicer, ISFILTERED was returning TRUE only in the non-subtotal cells.  Here, let’s go back:

Before the Slicer, Only These Rows Had a Filter Set on StoreName,
so ISFILTERED Returned TRUE Only for These Rows

But with the slicer, we get:

With the Slicer in Place and Selections Made,
ALL Cells in the Pivot Are Now Filtered on StoreName.

### Conclusion

In short, HASONEFILTER is the most reliable way to detect total cells.

But…  there’s an exception.

### One Final Point for HASONEVALUE

Let’s take StoreName off of the pivot and replace it with the StoreID field:

Swapping out StoreName for StoreID and Removing the Slicer Yields a Pivot Where Only HASONEVALUE “Outperforms” the Other Measures

Looking back, all three measure referenced the StoreName field directly:

IF(HASONEVALUE(Stores[StoreName]), [Units Sold], BLANK())

IF(ISFILTERED(Stores[StoreName]),[Units Sold],BLANK())

IF(HASONEFILTER(Stores[StoreName]),[Units Sold],BLANK())

Well, since StoreName is no longer on the pivot anywhere, there are no filters set on StoreName, so those two measures start going BLANK everywhere.

But HASONEVALUE is smarter.  StoreID=10 filters the Stores table to a single row, and that means just one value for StoreName as well:

When the Stores Table is Filtered to StoreID=10, There is Only One Value for StoreName:
This is Why HASONEVALUE Still Mostly Works

So, if you have lots of different fields that represent a store – StoreName, StoreID, StoreAddress, etc., and you plan to use different fields on different pivots, HASONEVALUE still might be the way to go.

So we still have choices to make from time to time

#### 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 8 Comments

1. Zack Barresse says:

Princess Bride and Apocalypse Now. Oh, and nice post. 😉

1. powerpivotpro says:

Nice Zack, nice. Shows your “range,” knowing both

1. Zack Barresse says:

It helps we watched Princess Bride last month. It’s a household thing here. And our kids had never seen Willow, so we indoctrinated them to that as well.

2. ruve1k says:

Rob, you should have included ISCROSSFILTERED for completeness.
On page 234 of your book you state it “is still quite foreign to me.” This post would’ve been the perfect place to experiment with it.

3. Working a (what seems imposable) problem, and one of this arguments may work, but its becoming a brain teaser for me, so if anyone sees this and has a solution, I will offer my: “Inconceivable” !! (also Princess Bride)

Problem:

PowerPivot:

Rows have Organizational Hierarchy (area, district, cluster, store)

Values have Calculated Fields

So looking to create a formula that would look like this (if it was a simple formula)

If(AND(District[Calculated Field A]>0, Cluster[Calculated Field B]>0), “True”, “False”)

Can this be done with HASONEVALUE or any other argument ?

Very much appreciate any help !

Larry

1. powerpivotpro says:

So you want to evaluate CalcFieldA purely in the context of District (like, at the District subtotal level, across all Clusters), and then similarly evaluate CalcFieldB at the Cluster level, across all Stores in that Cluster? If so, try this:

IF(
AND(
CALCULATE([CalcFieldA], ALL(your cluster table and column names), ALL(store table and column name)) > 0,
CALCULATE([CalcFieldA], ALL(store table and column name)) > 0
)
,”true”,”false”
)

1. So I have two points:

1) I _believe_ the second “CALCULATE” function should actually be referring to “CalcFieldB”, correct?

2) Assuming that that’s true, then it made a lot more sense why the formula for CalcFieldA doesn’t actually refer to Districts, and the formula for CalcFieldB doesn’t refer to clusters.

In both cases, you’re using “ALL()” to aggregate the data _within_ your target scope. Since you always want to report CalcFieldA at the “District” level, that means you need to lump together “ALL” the Clusters and “ALL” the Stores within each. (Similarly, if you wanted to aggregate “CalcFieldC” within each “Area”, you’d calculate it across “All Districts, Clusters and Stores”.)

Does that sound right?

4. When I use HASONEVALUE to filter out the sub totals I find that slicers do not work well. If the slicer is the same field as the sub total then the slicer thinks that field has not value. You can sill select the greyed out slicer options but they are greyed out or not there at all if you select “Hide items with no value” in slicer settings. Any way around this?