**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 onlyHASONEFILTER 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

**Subscribe to PowerPivotPro!**

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

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

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. 🙂

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.

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

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”

)

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?

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?

please can anyone tell me what hasonevalue does? i really dont knkw what it does?

I am trying to use this in the context of a calculated column rather than a measure in order to use a user selection as a variable

=

VAR

LimitSelection= IF(HASONEVALUE(WOSLimit[Limit]),WOSLimit[Limit])

RETURN

IF(WOS>LimitSelection,”ReturnValue1″,”ReturnValue2″)

This hasn’t worked for me in a calculated column. I have also tried creating a measure for the LimitSelection to replace the VAR in the above formula and referencing that measure in the return formula; however, that is not working either. It appears as though the context of the calculated column does not recognize that a selection has been made.

Does anyone know of a fix for this?