There are a few ways to work around the problem of duplicated rows in "stacked" data.
Update dataset to include a "response_row" that is 1 for the first row, 2 for the second, ....
- For views that don't include the multi-response questions, you can add a filter to the worksheet: response_row=1, preferably as a context filter. Context filters are evaluated before sets and other regular filters; right click the filter > Add to Context.
- If a view contains both, you can update your calculated fields; for example Number of Records = SUM(IF [response_row] == 1 THEN 1 ELSE NULL END)
- Single data source
- Need to remember to add the filter.
- Or update all measures with the IF response_row == 1, ... (safer but more work)
Use a separate data source for the multi-response questions, ...
- You'd use your original dataset as is, and the new dataset when you need the multi-response questions in the view.
- You could either 1) create a full data set with both types of questions, which will make it easier to put both types of questions in the view 2) create a data set with just the multi-response questions (safer, but more difficult to blend).
- Filters aren't normally required
- Need to maintain two data sources.
Use table calcs to select just the first row on these questions
- IF FIRST()==0 with addressing / compute using set to respondent ID, partitioned by question ID and all other dimensions.
- You don't have to modify your data source.
- Table calcs are complicated
- And slow on large datasets
- Addressing / compute using needs to be verified on each view
- If you're using other table calcs, you now have nested table calcs, which add complexity
I usually go with the second option, and use this data source only on the views with multi-response questions. I don't trust myself to remember that I need to add filters, especially if I haven't opened the workbook in six months.
On the other hand, if you have just a few measures / calculated fields you're using, the first option can work well.
Jim I am sincerely grateful for your advice - thank you ! I think I might go with the first option given I've only got a couple of calculated fields. You've reminded me that I should have also mentioned I don't have duplicated records for every single survey respondent. This is because I'm combining data from a 8 different surveys into one master file, and the open ended question was only asked in 6 of those surveys. More specifically, only 2/3 of survey respondents have multiple rows. But I can work around this in excel using an if / then statement to determine the appropriate text in the new "response_row" field. I'll let you know how I go - and thank you again.