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 zaraanderson ,
This happens because you have made the split to rows so the other columns get the values repeated, in this case you need to use a MIN or similar on the other responses where you don't want the repeat values.
Other options, can be to create a dimension table with the ID of the answer and the most important information of the answer, like date created completion time and those type of things, then remove create an index for each group of question and the clean the columns you don't need and just keep the values for the 1st row of each response.
If you don't want to have the duplicates another option can be to treat the column where you have the several answers (the original question you did) and make a disconnected table and make a measure that find if the answer is within you column then you can do your visualization.
Can you share a small sample please.
Hey MFelix!
This solution is working really well, but I just have a small question. Because I use branching on the form that generates the data, some of the data points are blank. So when I go to display the new "count" measures that were generated from your fantastic DAX stuff, I get "the arguments in GenerateSeries function cannot be blank".
I've done some investigating and have already found a solution, but I just don't know how to apply it to the DAX stuff you wrote. Apparently this will work:
But I'm not sure how to write this into your DAX. Could you please show me on the pet count example? I hope this will be my last problem!
HI zaraanderson ,
Are you refering that sometimes you don't have values for the responses?
In this case try to redo your measure to:
Pet Count = // Character that split phrase into words VAR SplitByCharacter = ";" // Temporary table that splits selected phrase into words VAR Words_table = ADDCOLUMNS ( GENERATE ( SELECTCOLUMNS ( FILTER ( SUMMARIZE ( ALLSELECTED ( 'Sheet1 (2)' ), 'Sheet1 (2)'[Respondent ID], 'Sheet1 (2)'[Pet type (select all that apply)] ), 'Sheet1 (2)'[Pet type (select all that apply)] <> BLANK () ), "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] ) ) )