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 it...
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.

     

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:

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        Theoretically, you can filter out those rows using the Query Editor but you may nit want to do so because a data point in some other column will also get removed in that process.