Multiple responses from forms combining

[zaraanderson]

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.

[zaraanderson]

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]

Hi,

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

=distinctcount(Data[ID})

Hope this heolps.

[zaraanderson]

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:

[MFelix]

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.

[zaraanderson]

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! 

[MFelix]

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] ) ) )

[zaraanderson]

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

[Ashish_Mathur]

Hi,

All you need is a distincount measure on the Respondent ID column.  Download my PBI file from here.

[zaraanderson]

I need to keep the data together. I'm not so concerned with the accurate respondent count as I am with keeping the data attached to various other data that I will use to slice (but need the slices to have accurate counts of a whole host of other data). does this make sense?

[Ashish_Mathur]

I do not understand what you mean by "Keeping data together".

[MFelix · Accepted solution]

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]

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 

[Ashish_Mathur]

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.

[zaraanderson]

Exactly. The logic I'm after I suppose is that if the response id# is the same as the row above, ignore certain columns? 

[Ashish_Mathur]

So that will not happen by removing the row.  That will happen within the DAX formula.  You will have to clarify what exactly do you want to calculate.

[MFelix]

Hi @zaraanderson ,

This depends on how you want to present the result but you need to use the split by delimiter option on the query editor only question is if you want a column for each of the values or a row:

https://docs.microsoft.com/en-us/power-query/split-columns-delimiter

Believe that you should go for the split by delimiter in rows