SELECTEDVALUE and HASONEVALUE in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that shows the total sales amount. When the report user selects a single product category, the report should display the sales for that category. If multiple or no categories are selected, the report should show the total sales for all categories with a message 'Multiple or no categories selected'.

📊 Data: You have sales data with columns: Date, ProductCategory, SalesAmount.

🎯 Deliverable: Create a measure that uses SELECTEDVALUE and HASONEVALUE to show sales amount based on the selection, and build a card visualization to display the result with the message when needed.

Sample Data

Date	ProductCategory	SalesAmount
2024-01-01	Electronics	1000
2024-01-02	Clothing	500
2024-01-03	Electronics	700
2024-01-04	Furniture	300
2024-01-05	Clothing	400
2024-01-06	Furniture	600
2024-01-07	Electronics	800
2024-01-08	Clothing	200

1

Step 1: Create a new measure named TotalSales that sums the SalesAmount column.

TotalSales = SUM(Sales[SalesAmount])

Expected Result

TotalSales measure sums all sales amounts, e.g., 4500 for the sample data.

2

Step 2: Create a new measure named SalesByCategory that uses SELECTEDVALUE to get the selected ProductCategory and shows sales for that category. If no single category is selected, show total sales with a message.

SalesByCategory = VAR SelectedCat = SELECTEDVALUE(Sales[ProductCategory], "Multiple or no categories selected") RETURN IF(HASONEVALUE(Sales[ProductCategory]), CALCULATE([TotalSales], Sales[ProductCategory] = SelectedCat), "Multiple or no categories selected: " & FORMAT(CALCULATE([TotalSales], ALL(Sales[ProductCategory])), "#,##0"))

Expected Result

When one category is selected, shows sales for that category (e.g., Electronics = 2500). When multiple or no categories selected, shows message with total sales (e.g., 'Multiple or no categories selected: 4500').

3

Step 3: Add a slicer visual to the report using the ProductCategory column to allow users to select categories.

Configure slicer with ProductCategory field.

Expected Result

User can select one or multiple categories to filter the report.

4

Step 4: Add a card visual to the report and set its value to the SalesByCategory measure.

Set card visual value to SalesByCategory measure.

Expected Result

Card shows sales amount for selected category or message with total sales if multiple/no categories selected.

Final Result

Electronics

↓

Furniture

✓Electronics category has the highest sales (2500).

✓When no or multiple categories are selected, total sales (4500) are shown with a message.

✓The measure correctly adapts to user selection using SELECTEDVALUE and HASONEVALUE.

Bonus Challenge

Modify the SalesByCategory measure to show sales amount for the selected category and also display the percentage of total sales that category represents.

Show Hint

Use DIVIDE function to calculate percentage: DIVIDE(Sales for selected category, TotalSales)

Practice

1. What does the SELECTEDVALUE function do in Power BI?

easy

A. Filters the data to only one selected value

B. Checks if exactly one value is selected

C. Returns all selected values as a list

D. Returns the single selected value or a default if multiple values are selected

Solution

1. Step 1: Understand the purpose of SELECTEDVALUE

   SELECTEDVALUE returns a single value if only one is selected; otherwise, it returns a default or blank.

2. Step 2: Compare with other options

   HASONEVALUE checks selection count, not returning the value itself; other options describe different behaviors.

3. Final Answer:

   Returns the single selected value or a default if multiple values are selected -> Option D

4. Quick Check:

   SELECTEDVALUE = single value or default [OK]

Hint: SELECTEDVALUE gives one value or default if many selected [OK]

Common Mistakes:

• Confusing SELECTEDVALUE with HASONEVALUE
• Thinking SELECTEDVALUE returns all selected values
• Assuming SELECTEDVALUE filters data

2. Which of the following is the correct syntax for using HASONEVALUE in a DAX measure?

easy

A. HASONEVALUE = ColumnName

B. HASONEVALUE[ColumnName]

C. HASONEVALUE(ColumnName)

D. HASONEVALUE(ColumnName, DefaultValue)

Solution

1. Step 1: Recall HASONEVALUE syntax

   HASONEVALUE takes a single column as argument to check if exactly one value is selected.

2. Step 2: Eliminate incorrect syntax

   Options A, C, and D do not follow correct function call syntax or add invalid parameters.

3. Final Answer:

   HASONEVALUE(ColumnName) -> Option C

4. Quick Check:

   HASONEVALUE(Column) syntax correct [OK]

Hint: HASONEVALUE takes one column as argument in parentheses [OK]

Common Mistakes:

• Using equal sign instead of parentheses
• Adding extra parameters to HASONEVALUE
• Using square brackets incorrectly

3. Given the following DAX measure:

Selected Product = SELECTEDVALUE('Products'[ProductName], "Multiple")

If the user selects exactly one product named "Chair", what will the measure return?

medium

A. "Chair"

B. "Multiple"

C. Blank

D. Error

Solution

1. Step 1: Understand SELECTEDVALUE behavior with one selection

   When exactly one value is selected, SELECTEDVALUE returns that value, here "Chair".

2. Step 2: Consider the default value

   The default "Multiple" is returned only if multiple or no values are selected, which is not the case here.

3. Final Answer:

   "Chair" -> Option A

4. Quick Check:

   One value selected = returns that value [OK]

Hint: One selected value returns it, else default [OK]

Common Mistakes:

• Assuming default value returns even with one selection
• Confusing blank with default
• Expecting error on multiple selections

4. You wrote this DAX measure:

IsSingleCategory = IF(HASONEVALUE('Sales'[Category]), SELECTEDVALUE('Sales'[Category]), "Multiple")

But it always returns "Multiple" even when one category is selected. What is the likely problem?

medium

A. The HASONEVALUE function is used incorrectly without a filter context

B. SELECTEDVALUE cannot be used inside IF statements

C. The column name 'Category' is misspelled

D. The measure lacks a default value in SELECTEDVALUE

Solution

1. Step 1: Analyze HASONEVALUE usage

   HASONEVALUE depends on filter context; if no filter is applied, it returns FALSE even if one value exists.

2. Step 2: Understand why measure returns "Multiple"

   Without proper filter context, HASONEVALUE returns FALSE, so IF returns "Multiple" always.

3. Final Answer:

   The HASONEVALUE function is used incorrectly without a filter context -> Option A

4. Quick Check:

   HASONEVALUE needs filter context to work [OK]

Hint: HASONEVALUE needs filter context to return TRUE [OK]

Common Mistakes:

• Assuming SELECTEDVALUE can't be inside IF
• Ignoring filter context importance
• Not checking column spelling

5. You want to create a measure that shows the selected product name if exactly one product is selected, otherwise shows "Multiple Products". Which DAX expression correctly achieves this?

hard

A. Product Selected = IF(SELECTEDVALUE('Products'[ProductName]), HASONEVALUE('Products'[ProductName]), "Multiple Products")

B. Product Selected = IF(HASONEVALUE('Products'[ProductName]), SELECTEDVALUE('Products'[ProductName]), "Multiple Products")

C. Product Selected = SELECTEDVALUE('Products'[ProductName], HASONEVALUE('Products'[ProductName]))

D. Product Selected = HASONEVALUE('Products'[ProductName]) & SELECTEDVALUE('Products'[ProductName])

Solution

1. Step 1: Understand the requirement

   Show product name if exactly one selected; else show "Multiple Products".

2. Step 2: Evaluate each option

   Product Selected = IF(HASONEVALUE('Products'[ProductName]), SELECTEDVALUE('Products'[ProductName]), "Multiple Products") uses HASONEVALUE to check selection count and SELECTEDVALUE to get the name, with fallback text. Others misuse functions or syntax.

3. Final Answer:

   Product Selected = IF(HASONEVALUE('Products'[ProductName]), SELECTEDVALUE('Products'[ProductName]), "Multiple Products") -> Option B

4. Quick Check:

   HASONEVALUE + SELECTEDVALUE with IF = correct pattern [OK]

Hint: Use IF with HASONEVALUE and SELECTEDVALUE for conditional display [OK]

Common Mistakes:

• Passing HASONEVALUE as default to SELECTEDVALUE
• Using SELECTEDVALUE as condition in IF
• Concatenating HASONEVALUE and SELECTEDVALUE incorrectly