The SELECTEDVALUE function in DAX is a powerful tool designed to return a single value from a column when one, and only one, value is selected. If no value or multiple values are selected, it returns either a default value (if provided) or BLANK().
Syntax:
SELECTEDVALUE(<ColumnName>[, <DefaultValue>])
- <ColumnName>: The column containing the value to retrieve.
- <DefaultValue>: (Optional) The value returned when no single value is selected.
How SELECTEDVALUE Works
When exactly one value is selected from the specified column, SELECTEDVALUE retrieves it. If multiple values are selected, or no selection is made, the function avoids errors by returning the specified default value or BLANK(). This makes it extremely useful for creating dynamic reports and handling slicer-based selections gracefully.
Insights from SQLBI on SELECTEDVALUE
As highlighted in the SQLBI video Using the SELECTEDVALUE function in DAX, SELECTEDVALUE simplifies code that previously required the use of HASONEVALUE or IF statements. For example, instead of writing:
DAXCopy IF(HASONEVALUE(Table[Column]), VALUES(Table[Column]), BLANK())
You can achieve the same functionality with:
DAXCopy SELECTEDVALUE(Table[Column])
This not only makes your code cleaner and easier to read but also aligns well with modern DAX best practices.
When to Use SELECTEDVALUE
1. Dynamic Report Titles
SELECTEDVALUE is ideal for dynamically updating report titles based on slicer selections.
Example:
Create a report title reflecting the selected region or default to “All Regions” if none is chosen.
DAX Formula for Dynamic Title:
Dynamic Title = "Sales Report - " & SELECTEDVALUE(Regions[Region], "All Regions")
Result:
- Single region selected: “Sales Report – North America”
- No selection: “Sales Report – All Regions”
2. Customizing Measures Based on Selection
Adjust measure calculations dynamically depending on slicer selections.
Example:
Display average sales for a selected product category, or overall sales if none is selected.
DAX Formula:
Category Average Sales =
IF(
ISBLANK(SELECTEDVALUE(Products[Category])),
AVERAGE(Sales[Sales Amount]),
CALCULATE(
AVERAGE(Sales[Sales Amount]),
Products[Category] = SELECTEDVALUE(Products[Category])
)
)
Result:
- Selected category: Average sales for that category.
- No selection: Overall average sales.
3. Conditional Formatting with Slicer Selections
Define thresholds or rules for conditional formatting based on slicer selections.
Example:
Highlight sales below a region-specific threshold.
DAX Formula for Threshold:
Threshold =
SWITCH(
SELECTEDVALUE(Regions[Region]),
"North America", 5000,
"Europe", 3000,
"Asia", 2000,
1000 -- Default
)
DAX Formula for Formatting:
Format Sales = IF(Sales[Sales Amount] < [Threshold], 1, 0)
4. Filtering Data in Visuals
Show data dynamically based on a single selection or display all data when no selection is made.
Example:
Filter a table to display only sales for the selected product.
DAX Formula:
Product Filter =
IF(
ISBLANK(SELECTEDVALUE(Products[Product Name])),
1,
IF(Sales[Product Name] = SELECTEDVALUE(Products[Product Name]), 1, 0)
)
5. Debugging and Error Handling
Use SELECTEDVALUE to identify issues with slicer selections or provide meaningful fallback values.
Example:
Display a default message when no single product is selected.
DAX Formula:
Selected Product Debug =
SELECTEDVALUE(Products[Product Name], "Multiple or None Selected")
Best Practices for SELECTEDVALUE
- Use Default Values: Always specify a default value to avoid blank results and improve user experience.
- Simplify Code: Replace complex HASONEVALUE and VALUES logic with SELECTEDVALUE for cleaner and more maintainable code.
- Combine with Logical Functions: Pair SELECTEDVALUE with IF, SWITCH, or CALCULATE for enhanced functionality.
- Debugging Aid: Leverage SELECTEDVALUE during development to troubleshoot slicer-related behaviors.
Conclusion
SELECTEDVALUE is a versatile DAX function that simplifies handling single-value selections in Power BI. Whether you’re creating dynamic titles, customizing measures, or building conditional formatting, SELECTEDVALUE enhances both the functionality and readability of your DAX formulas.
For more in-depth learning, be sure to check out SQLBI’s video on SELECTEDVALUE, which highlights its practical applications and best practices!