HASONEVALUE vs. ISFILTERED vs. HASONEFILTER

 
HASONEVALUE vs ISFILTERED vs HASONEFILTER - Three Similar PowerPivot DAX Functions

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?)

HASONEVALUE vs ISFILTERED vs HASONEFILTER - Impact of Slicers

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:

image

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:

HASONEVALUE vs ISFILTERED vs HASONEFILTER - Impact of Slicers

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:

image

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:

image

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 Smile

7 Responses to HASONEVALUE vs. ISFILTERED vs. HASONEFILTER

  1. Zack Barresse says:

    Princess Bride and Apocalypse Now. Oh, and nice post. ;)

  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. larry says:

    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

    • 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”
      )

      • 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?

Leave a Reply