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.