Power Pivot Principles: HASONEFILTER vs. HASONEVALUE vs. ISFILTERED

Welcome back to the Power Pivot Principles blog.  This week, we will compare the HASONEFILTER function to the HASONEVALUE and ISFILTERED functions in DAX.

To recap, The HASONEFILTER function returns TRUE when the number of directly filtered values on columnName is one (1); otherwise, it will return FALSE.  The HASONEFILTER function has the following syntax:

The HASONEVALUE function returns TRUE when the context of a specific column has been filtered down to one distinct value only; otherwise, the function returns FALSE.  The HASONEVALUE follows the syntax:

The ISFILTERED function has the following syntax:

This returns a TRUE value when the columnName parameter is filtered in the PivotTable (by definition, all simple, non-total rows and columns in a PivotTable are filtered by a given context).  If there is no filter applied to the columnName, the function will return with FALSE.

Let’s get illustrated by returning to our Sales data example:

The HASONEFILTER Test and the Total Sales (HASONEFILTER) measures have been created to illustrate the HASONEFILTER function:

We will create similar Test and Total Sales measures for the HASONEVALUE and the ISFILTERED functions:

Returning to Excel, we create a PivotTable by dragging the Product field to Columns and the six measures to Values.  In addition, we create two Slicers: Product and Store Key.  At first glance, when nothing is chosen from the Slicers, all the three functions seem to behave in the same way:

When we select one product Accessories from the Product slicer, all the three functions show TRUE in the Test columns and Total Sales as well as Grand Total Sales in the Total Sales columns:

The similar thing happens when one filter is applied in the Store Key slicer:

However, if we select two fields, say, Accessories and Clothing in the Product slicer, only the ISFILTERED Test and Total Sales (ISFILTERED) columns display TRUE / a value in the Grand Total row:

Similar results occur when we select multiple fields from the Store Key slicer. It is because among the three functions, only the ISFILTERED function returns values for multiple direct filters:

Let’s add an additional table called ‘Product List’, which stores the list of products. We load this table into the Power Pivot Data Model:

In the diagram view, we define the relationship between tables by connecting the Product field from the Sales table with the ‘Product from Product List’ in the ‘Product List’ table:

Returning to Excel, we create a Slicer with ‘Product from Product List’. If we select one product, say Accessories, from this Slicer, only the HASONEVALUE Test and Total Sales (HASONEVALUE) columns show TRUE and a Total Sales number respectively in the Grand Total row. While the HASONEVALUE Test column returns FALSE for the four unchosen products, the HASONEFILTER Test and the ISFILTERED Test column return TRUE for all products regardless.

If we select one more product from the Slicer, the HASONEVALUE Test and Total Sales (HASONEVALUE) columns will no longer show TRUE in Grand Total Sales:

In summary:

• the HASONEFILTER function: provides a single return for a direct filter
• the HASONEVALUE function: provides a single return for a direct and / or cross filter
• the ISFILTERED function: provides a single return for multiple direct filters.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about >here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.