[DAX] HASONEVALUE vs HASONEFILTER

Summary

Tips for DAX Functions

HASONEFILTER

Syntax

HASONEFILTER(<ColumnName>)

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

  • 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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store