cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
calerof Member
Member

Rate a Microsoft Forms Survey with choice questions using LOOKUPVALUE

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:

  • Questions, including only 5 questions with 5 choices each, i.e.
    • Always, Most time, Some time, Almost never, Never.
  • Rating_table: Valuation of answers, each question has its own valuation, as follows (I unpivoted it in Power Query):rating.png
  • Answers of two survey participants for this sample:
  • Answers.png

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:

sample valuation.png

PBIX file

 

2. Actual Data

 

Same procedures. Here are my files:

Answers

Questions

Valuation table

 

Code:

Respuesta Valor = 
LOOKUPVALUE(
    Tabla51[Valor],
    Tabla51[Opcion],
    Respuestas[Respuesta],
    Tabla51[Item],
    Respuestas[Pregunta Num]
)

PBIX file

 

I'd really appreciate your support.

 

Best regards,

 

Fernando

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Rate a Microsoft Forms Survey with choice questions using LOOKUPVALUE

Hi @calerof ,

 

When you use the lookupvalue, you condition may filter more than 1 rows, you can test it like following.

 

13.PNG14.PNG15.PNG

 

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.

 

16.PNG


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.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Rate a Microsoft Forms Survey with choice questions using LOOKUPVALUE

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
calerof Member
Member

Re: Rate a Microsoft Forms Survey with choice questions using LOOKUPVALUE

Thanks Tom, I'll review it right away.

Fernando

 

Community Support Team
Community Support Team

Re: Rate a Microsoft Forms Survey with choice questions using LOOKUPVALUE

Hi @calerof ,

 

When you use the lookupvalue, you condition may filter more than 1 rows, you can test it like following.

 

13.PNG14.PNG15.PNG

 

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.

 

16.PNG


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.

View solution in original post

calerof Member
Member

Re: Rate a Microsoft Forms Survey with choice questions using LOOKUPVALUE

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 477 members 4,089 guests
Please welcome our newest community members: