Forum Discussion
Return result based on if statement: Error "Single value can't be determined"
Hello,
I have a table called “Data”. This table has the three columns “Customer”, “Metric” and “Value”.
- “Customer” contains customer names in text.
- “Metric” contains the metric names in text
- “Value” contains the result of the customer and metric combination in that specific line.
The same customers appear once for each characteristic of “Metric”. The “Data” table looks like this:
| Customer | Metric | Value |
| A | Metric 1 | 3 |
| A | Metric 2 | 2 |
| A | Metric 3 | 1 |
| B | Metric 1 | 2 |
| B | Metric 2 | 3 |
| B | Metric 3 | 2 |
| C | Metric 1 | 1 |
| C | Metric 2 | 2 |
| C | Metric 3 | 3 |
| D | Metric 1 | 2 |
| D | Metric 2 | 1 |
| D | Metric 3 | 3 |
| E | Metric 1 | 1 |
| E | Metric 2 | 1 |
| E | Metric 3 | 2 |
| F | Metric 1 | 3 |
| F | Metric 2 | 2 |
| F | Metric 3 | 2 |
I want to write a measure in DAX that checks for the number in “Value” for the metric in “Metric” and performs a specific action based on the combination of metric and value. I tried it with the following measure:
Measure =
IF(‘Data’[Metric] = “Metric 1” && “Data”[Value]=3, “Top score”,
IF(‘Data’[Metric] = “Metric 1” && “Data”[Value]=2, “Mid score”,
IF(‘Data’[Metric] = “Metric 1” && “Data”[Value]=1, “Low score”)
I would create this measure for each existing metric.
At the end I want to select one customer and show the results of the IF statement (Metric 1 = Top score, Metric 2 = Low Score, …) in a format I have not yet decided on, probably in a table.
When I write this measure in PowerBI I get the error:
“A single value for column 'Metric' in table 'Data’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.”
I assume that this is because the metrics in “Metric” appear once for each customer. I tried to figure this out with other ways like CALCULATETABLE, SUMX, and so on, but I couldn’t get it to work.
Can you help me formulating a measure that can do what I want to?
Any help is appreciated!
try this in calculated column,
Score Distribution = SWITCH( TRUE(), 'Table'[Metric] ="Metric 1" && 'Table'[Value] =3 ,"Top Score", 'Table'[Metric] ="Metric 1" && 'Table'[Value] =2 ,"Mid Score", 'Table'[Metric] ="Metric 1" && 'Table'[Value] =1 ,"Low Score", 'Table'[Metric] ="Metric 2" && 'Table'[Value] =3 ,"Top Score", 'Table'[Metric] ="Metric 2" && 'Table'[Value] =2 ,"Mid Score", 'Table'[Metric] ="Metric 2" && 'Table'[Value] =1 ,"Low Score", 'Table'[Metric] ="Metric 3" && 'Table'[Value] =3 ,"Top Score", 'Table'[Metric] ="Metric 3" && 'Table'[Value] =2 ,"Mid Score", 'Table'[Metric] ="Metric 3" && 'Table'[Value] =1 ,"Low Score")I have attached the file for reference.
3 Replies
Do you need Measure or Calculated Column?
Hello, thank you for your reply.
Whatever works to show the result of the IF statement evaluation.
At the end I want to select one customer with a slicer or filter and display the results of only this customer in a table or text field, whatever will work.
try this in calculated column,
Score Distribution = SWITCH( TRUE(), 'Table'[Metric] ="Metric 1" && 'Table'[Value] =3 ,"Top Score", 'Table'[Metric] ="Metric 1" && 'Table'[Value] =2 ,"Mid Score", 'Table'[Metric] ="Metric 1" && 'Table'[Value] =1 ,"Low Score", 'Table'[Metric] ="Metric 2" && 'Table'[Value] =3 ,"Top Score", 'Table'[Metric] ="Metric 2" && 'Table'[Value] =2 ,"Mid Score", 'Table'[Metric] ="Metric 2" && 'Table'[Value] =1 ,"Low Score", 'Table'[Metric] ="Metric 3" && 'Table'[Value] =3 ,"Top Score", 'Table'[Metric] ="Metric 3" && 'Table'[Value] =2 ,"Mid Score", 'Table'[Metric] ="Metric 3" && 'Table'[Value] =1 ,"Low Score")I have attached the file for reference.
