DAX – HASONEVALUE vs. ISFILTERED vs. HASONEFILTER

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