Power Pivot Principles: The A to Z of DAX Functions – HASONEFILTER

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at HASONEFILTER. 

The HASONEFILTER function

The HASONEFILTER function is one of the information functions.  It returns TRUE when the number of direct filtered values on columnName is one; otherwise, it returns FALSE.  It employs the following syntax:

This function has one parameter:

• columnName: this is required and represents the name of an existing column, using standard DAX syntax and it cannot be an expression.

It has the following remarks:

• this function is similar to HASONEVALUE function with the difference that HASONEVALUE works based in cross-filters while HASONEFILTER works by a direct filter
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Let’s consider the following Table call tbl_Example:

We can write a DAX to check if Group has only one [1] filter:

Then we may place this measure onto the PivotTable to check the result:

We can add more inputs in the tbl_Example:

After refresh the Data Model the result is as follow:

We can use this function inside the CALCULATE function:

The CALCULATE function will filter out the tbl_Example for value “A” only then the HASONEFILTER will perform its function which give us the following:

However, if the filter context of CALCULATE has two [2] (or more) filters, even though the output data will only contain “A”, the HASONEFILTER will output a FALSE result, viz.

It will output the following:

Come back next week 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.