HASONEFILTER
Returns TRUE when exactly one filter is applied directly to a column, whatever the number of values in that filter.
HASONEFILTER(<column>)Returns: TRUE or FALSE.
What it does
HASONEFILTER checks whether there is a single filter on a column. It is easy to confuse with HASONEVALUE. HASONEVALUE asks "is exactly one value visible?" HASONEFILTER asks "is there one filter on this column?" A slicer with several items selected is one filter that holds several values. So HASONEFILTER is TRUE, and HASONEVALUE is FALSE. Use it when you need to know whether a filter was applied at all, not how many values it holds.
Syntax and arguments
- column: a column of the model. Give the column, not a table.
Example
A report title changes when the user has narrowed the store list, so readers know the figures are not company-wide.
Store Filter Note =
IF (
HASONEFILTER ( Stores[StoreName] ),
"Showing the selected stores",
"Showing all stores"
)
With a slicer that has several stores selected, the first text shows. With no store slicer, the second text shows.
Example: a second use
Tell apart a slicer with several channels chosen from a single channel selection.
Channel Filter Check =
IF (
HASONEFILTER ( Channel[ChannelName] )
&& NOT ( HASONEVALUE ( Channel[ChannelName] ) ),
"Several channels chosen in one filter",
"Other case"
)
The test is TRUE when the slicer holds more than one channel. The other cases are a single channel, or no filter at all.
Things to watch
- Be clear whether you need a count of filters or a count of values. Most label logic needs
HASONEVALUEorSELECTEDVALUE. - Rows in a matrix and axis fields also act as filters, so a card and a matrix can disagree about the same column.
- This is an advanced test. Check that the function is available in your version of Power BI or Power Pivot before you use it in a shared report.
- Test the result with a slicer that holds one value, several values and none.
In business terms
Dashboards that change their heading when a user narrows the view to one entity, and checks that confirm a report was filtered before figures were exported to a board pack.