Forum Discussion
Multiple responses from forms combining
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.
Hi MFelix - thanks for your reply again, really appreciate your time. I can't share a sample of the actual file, but I have made a dummy file that replicates the characteristics. Link to excel file and PBI file
So basically, I have a few multiple response questions (eg. pet type, favourite foods) and if I split all those by delimiter then my file will eventually have hundreds of duplicates. For the multiple responses, I want to split them and count them individually (eg. view how many people have dogs, how many people have birds, how many people have cats) and have no interest in the combination of pets that people have. However, I need to keep the data connected to its original response so that I can use the other single response question data points as filters/slicers (eg. if we just look at 20-29 year olds, what is the dispersion of pet type?)
Hopefully this makes sense? Again I really appreciate you taking such a close look at this.
Kind regards,
Zara
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.
Hi MFelix,
Wow I think this works. Now I have to figure out how to apply it to my actual dataset. Hopefully I can slog through that, and then let you know when I am successful. Hopefully I won't have anymore questions, but my knowledge isn't great so hopefully I can deduce without any trouble.
Thanks,
Zara