Daily DAX : Day 332 HASONEFILTER

HASONEFILTER DAX Function

Description

The HASONEFILTER function in Power BI DAX checks if a column has exactly one value filtered in the current context. It returns TRUE if the column is filtered to a single value, otherwise FALSE.

Syntax

HASONEFILTER(<columnName>)

• <columnName>: The name of the column to check for a single filter.

Return Value

TRUE or FALSE (Boolean).

Use Case

HASONEFILTER is useful in scenarios where you need to apply conditional logic based on whether a single value is selected in a slicer or filter. It’s commonly used in measures to customize calculations or display different results when a single filter is applied.

Example

Suppose you have a table Sales with columns Region and SalesAmount. You want to display a specific message when a single region is selected in a slicer.

RegionMessage = 
IF(
    HASONEFILTER(Sales[Region]),
    "Single region selected: " & SELECTEDVALUE(Sales[Region]),
    "Multiple or no regions selected"
)

Explanation: If a single Region is filtered (e.g., via a slicer), the measure returns a message with the selected region’s name. Otherwise, it indicates multiple or no regions are selected.

Practical Application

• Slicers: Use in dashboards to detect when a user selects a single value in a slicer for tailored calculations.
• Dynamic Measures: Adjust calculations based on whether a single filter is applied, such as showing detailed metrics for a single category.
• Conditional Formatting: Apply specific formatting or text outputs when a single filter context is detected.

Notes

• HASONEFILTER only checks for a single filter value, not the absence of filters or multiple filters.
• Combine with SELECTEDVALUE to retrieve the filtered value when HASONEFILTER is TRUE.
• It evaluates the filter context at the time of calculation.