HASONEVALUE
Returns TRUE when a column has exactly one distinct value in the current filter context.
HASONEVALUE(<columnName>)Returns: TRUE or FALSE (a scalar).
What it does
HASONEVALUE tells you whether a column has only one value visible in the current filter context. It is used to decide whether a measure makes sense: a budget for one department is meaningful, a total budget for several is not. Typical uses are a message that asks the user to select one item, or a measure that shows a figure only when one item is chosen.
Syntax and arguments
- columnName: a single column reference, such as
Stores[StoreName]. It must be a column, not a table.
Example
The store manager's dashboard shows a store's staff cost per hour. The figure only makes sense for one store, so the measure is blank when several stores are selected.
Staff Cost per Hour =
IF(
HASONEVALUE(Stores[StoreName]),
DIVIDE([Staff Cost], [Hours Worked])
)
When one store is picked, the ratio shows. With no selection or with several stores, HASONEVALUE is false and the measure is blank, rather than a blended figure that means nothing.
Example: a second, different use
A message card that tells the user what to do when the report is not yet filtered to one entity:
Selection Note =
IF(
HASONEVALUE(Entities[EntityName]),
"Showing " & VALUES(Entities[EntityName]),
"Select one entity to see its trial balance"
)
VALUES returns the one name in context, which is safe because HASONEVALUE is true at that point. The message keeps the reader out of an invalid view.
Things to watch
- HASONEVALUE is TRUE when there is exactly one value. A column with no filter and only one row in the model is also TRUE.
- Use SELECTEDVALUE when you want the value itself, with a fallback.
- The test looks at the filter context of the cell. In a matrix row for one store, the answer is TRUE for that row only.
- Do not use HASONEVALUE as security. It is a display test.
In business terms
HASONEVALUE keeps per-unit and per-entity measures honest. A rate, a per-head figure or a trial balance should only show when one unit is selected, so a controller never reads a blended number as if it were one entity's.