[DAX] HASONEVALUE vs HASONEFILTER

Sometimes it’s confusing for us to decide when to use HASONEVALUE() or HASONEFILTER(). Here we will talk about the difference between the two functions and some tips about when to use.

Summary

Tips for DAX Functions

• HASONEVALUE() works based on cross-filters, while HASONEFILTER() works by a direct filter.

HASONEFILTER

Syntax

HASONEFILTER(<ColumnName>)

• Return TRUE if the number of directly-filtered values on the ColumnName is one.
• Take a look at the column in the table and see how many values are being explicitly filtered by the slicer/filter. (Not implicitly filtered through a relationship but explicitly filtered by clicking on the slicer/filter.)

Situation

• Works for directly-filtered Columns / directly-filtered slicers

HASONEVALUE

Syntax

HASONEVALUE(<ColumnName>)

• Return TRUE if only one value is visible regardless of that value is being filtered, whether it’s an explicit filter or implicit filter due to a relationship.
• Allows us to identify if the expression is being evaluated in the context of a single value for Column Name.
• An equivalent expression for HASONEVALUE() is COUNTROWS(VALUES(<columnName>)) = 1.

Situation

• Unexpected Totals
  When we get an unexpected total in Power BI, in order to get rid of the total, we can simply add HASONEVALUE to our measure.
  For example:
  IF( HASONEVALUE(<ColumnName>), [Measure], BLANK() )
• RANKX: “Total” row is showing a rank of 1
  When using RANKX(), if the “Total” row is showing a rank of 1, use IF & HASONEVALUE with RANKX to exclude it from the rank.

Tips to Differentiate

1. Determine if there is any explicit slicers/filters
2. Are the explicit slicers/filters direct-filtered or cross-filtered?
3. Be careful about the “Total Value” in the visuals