Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm trying to rate a survey made with Microsoft Forms with choice questions using the LOOKUPVALUE function.
I made a sample data file and it's working, but in my actual data it's not, I receive the error message:
"A table of multiple values was supplied where a single value was expected".
1. Sample data
I have three tables in this excel file:
After cleaning the data I try to value each answer with this code:
Answer value = LOOKUPVALUE( Rating_table3[Value], Rating_table3[Option], Answers1[Answer], Rating_table3[Question number], Answers1[Question Number] )
This works fine:
2. Actual Data
Same procedures. Here are my files:
Code:
Respuesta Valor = LOOKUPVALUE( Tabla51[Valor], Tabla51[Opcion], Respuestas[Respuesta], Tabla51[Item], Respuestas[Pregunta Num] )
I'd really appreciate your support.
Best regards,
Fernando
Solved! Go to Solution.
Hi @calerof ,
When you use the lookupvalue, you condition may filter more than 1 rows, you can test it like following.
if you just want negativa value, we can use the following formula.
Respuesta Valor = VAR t = CALCULATE ( COUNTROWS ( 'Tabla51' ), FILTER ( 'Tabla51', Tabla51[Opcion] = [Respuesta] && Tabla51[Item] = [Pregunta Num] ) ) VAR result = IF ( t = 1, LOOKUPVALUE ( Tabla51[Valor], Tabla51[Opcion], Respuestas[Respuesta], Tabla51[Item], Respuestas[Pregunta Num] ), VAR s = "Negativa" -- or other conditon you want RETURN MAXX ( FILTER ( 'Tabla51', Tabla51[Opcion] = [Respuesta] && Tabla51[Item] = [Pregunta Num] && Tabla51[Expresividad] = s ), [Valor] ) ) RETURN result
or jump the judge, just use the maxx/minx function.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey,
first things first, thanks providing a great explanation and all the sample data you provided as well.
As I'm currently running out of time I will provide just this link: https://www.daxpatterns.com/survey/
The article explains how survey data can be analyzed using a tabular model.
Regards,
Tom
Thanks Tom, I'll review it right away.
Fernando
Hi @calerof ,
When you use the lookupvalue, you condition may filter more than 1 rows, you can test it like following.
if you just want negativa value, we can use the following formula.
Respuesta Valor = VAR t = CALCULATE ( COUNTROWS ( 'Tabla51' ), FILTER ( 'Tabla51', Tabla51[Opcion] = [Respuesta] && Tabla51[Item] = [Pregunta Num] ) ) VAR result = IF ( t = 1, LOOKUPVALUE ( Tabla51[Valor], Tabla51[Opcion], Respuestas[Respuesta], Tabla51[Item], Respuestas[Pregunta Num] ), VAR s = "Negativa" -- or other conditon you want RETURN MAXX ( FILTER ( 'Tabla51', Tabla51[Opcion] = [Respuesta] && Tabla51[Item] = [Pregunta Num] && Tabla51[Expresividad] = s ), [Valor] ) ) RETURN result
or jump the judge, just use the maxx/minx function.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-lid-msft ,
Thank you very much for your response and the time to review my model. After reviewing your solution and rechecking my data, you made me realize that in my Valuation table (Table51) I had a duplicated record, i.e. question # 54, thus producing the error in the LOOKUPVALUE calculated column. After eliminating the duplicated value the code works smoothly:
Respuesta Val = LOOKUPVALUE( Tabla51[Valor], Tabla51[Opcion], Respuestas_valuadas1[Respuesta], Tabla51[Item], Respuestas_valuadas1[Pregunta_Num] )
It's always great to have the Community's support.
Cheers,
Fernando
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |