Forum Discussion

zaraanderson's avatar
zaraanderson
Frequent Visitor
4 years ago
Solved

Multiple responses from forms combining

Hi everyone,

 

I've tried my best to research this, but I'm a Power BI newbie and a bit is going over my head.

 

I am collecting data via a "Forms for Excel" survey. I have a multiple response item, where respondents can choose any combination of 5 options. However, when I look in Power BI, the data is being shown as the combination, rather than counting the individual times each item is chosen.

 

So in the below snip, you can see Critical and Creative Thinking; Ethical Understanding; Intercultural Understanding as a category, because on one survey response these 3 were chosen in combination on a multiple response question.

But I would like Power BI to represent Critical and Creative Thinking as a separate category, followed by Ethical Understanding as a separate category, and so on and so forth.

 

All of my research on how to fix this refers to Flow and Sharepoint, but as I used the Forms for Excel, I didn't have to do the Flow or Sharepoint List...

 

Any help would be greatly appreciated.

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi zaraanderson ,

     

    In this case you need to take a different approach, keeping the responses has you have you can create disconnected tables with the answrs from the multioption. In this case I have created 2 tables:

     

    Now using this I have created the following two measures:

    Pet Count =
// Character that split phrase into words
VAR SplitByCharacter = ";" // Temporary table that splits selected phrase into words
VAR Words_table =
    ADDCOLUMNS (
        GENERATE (
            SELECTCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ( 'Sheet1 (2)' ),
                    'Sheet1 (2)'[Respondent ID],
                    'Sheet1 (2)'[Pet type (select all that apply)]
                ),
                "Find_Text", 'Sheet1 (2)'[Pet type (select all that apply)]
            ),
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "Word", PATHITEM ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ), [Value] )
    )
RETURN
    COUNTROWS ( FILTER ( Words_table, [Word] IN VALUES ( Pets[Pet type] ) ) )



Food Count =
// Character that split phrase into words
VAR SplitByCharacter = ";" // Temporary table that splits selected phrase into words
VAR Words_table =
    ADDCOLUMNS (
        GENERATE (
            SELECTCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ( 'Sheet1 (2)' ),
                    'Sheet1 (2)'[Respondent ID],
                    'Sheet1 (2)'[Favourite foods (select all that apply)]
                ),
                "Find_Text", 'Sheet1 (2)'[Favourite foods (select all that apply)]
            ),
            VAR TokenCount =
                PATHLENGTH ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ) )
            RETURN
                GENERATESERIES ( 1, TokenCount )
        ),
        "Word", PATHITEM ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ), [Value] )
    )
RETURN
    COUNTROWS ( FILTER ( Words_table, [Word] IN VALUES ( Foods[Favourite foods] ) ) )

     

    Has you can see now you can use these two measures on your calculation:

    PBIX attach.

     

16 Replies

  • zaraanderson's avatar
    zaraanderson
    Frequent Visitor
    4 years ago

    Thanks MFelix, I so appreciate your time. So I found that split by delimiter in rows definitely fixed my visuals. Everything is nicely in the independent categories now. 

     

    However, it's caused a different problem which I'm not sure how to deal with. Basically in the PowerQuery, it's created duplicates of each response when it has split, which is making it look like I have way more responses than i do...

     

    Is there a way I can add another fix over the top so I can have an accurate representation of the number of respondents?

     

    Thanks,

     

    Zara

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      The number of respondents would now be calculated by the following measure:

      =distinctcount(Data[ID})

      Hope this heolps.

      • zaraanderson's avatar
        zaraanderson
        Frequent Visitor
        4 years ago

        Thanks Ashish_Mathur for jumping in. I guess the problem is moreso that I have a few more data points with multiple responses that I need to split, which will essentially make my data set grow ridiculously large and effect further other data that I wish to display from other columns, which will have a huge number of duplictaes and then therefore not be accurate? 

         

        So here, I have 3 respondents (2 primary, 1 secondary), but because of the split, on my visualisation it now says there are 7 primary and 3 secondary, which of course is inaccurate. Is there a way to fix this? I'd essentially like it to know what is dupllicate and ignore it ğŸ˜‚ as seen in my very technical drawing here: