I am working with survey data collected for Overseas Visitors to US. I have 8 years of data that at this point in my data analysis has 600+ variables, mostly due to multiple response (MR) questions. I have been thru all of the Power Query and Power Pivot courses but I am still having some issues wrapping my head around how to even setup a multiple response table/visualization that I can tie to slicers (for me they would be for Year and Country of Residence both of which have single column answers). I was thinking I would need to do an unpivot with my data model to create something I can use. An added glitch in this is that I also need to weight the data. I could really use some help on this. It's frustrating since I have been programming in SAS (statistical programming language for over 30 years) and I know how to get around the MR issues for presenting really boring Excel Tables. I know I can create measures to count the number of respondents and sum the final weight figure, just not how to create a table that slicers apply to unless I am missing something basic. Any help on this would be greatly appreciated.
Hi Laura,
It's difficult to be specific as the screenshot doesn't give a lot of information, but I'd structure your data with the following columns:
- Year
- Country
- Question
- Responses
From there you can add a column for the weighting from a different table that presumably stores the question list and weighting. In the attached file I created some dummy data based on your description above so you can see how Slicers might work with a PivotTable (Power BI matrix table).
I hope that points you in the right direction.
Mynda
Thank you for the attached file. So taking this example and putting into practice, I would unpivot my select columns making sure I include the year and country variables from my base imported file making the query a reference to my base. If I do this and add data for other years, the unpivoted data will update when I refresh my model?
Hi Laura,
Yes. Follow the Power Query lesson 2.05 Get data from a folder to see which query you should be performing the unpivot in so that any new data is also unpivoted before being added to the final output.
Mynda
So in other words, if I unpivot the data in a separate query/table I have to include any variables for the slicers in the definition, correct? In the future I need to be able to include States Visited as a slicer, so that adds a whole other level of complexity...
Hi Laura,
Presumably the States Visited with be another question, so you'd need that information in the Responses column. If you 'unpviot other columns' then any new columns added in future data sets will automatically be included in the unpivot.
If my answer doesn't make sense it's because I'm not really sure what you're referring to without seeing an example of the data. If you can share a sample file then I can be more certain that I understand and I'll be able to give you a more accurate answer.
Mynda
Dear Mynda,
I was able to figure out how to apply the slicers to my region of destination once I created a pivot/unpivot table and measures that contained the country of residence variable (no year since I only have one year of data in my model and I had not thought ahead to that point yet)... Yay!
The question of slicers applying to multiple response leads me to ask: how can slicers from the main page of my report be applied to all pages/visualizations in a report without having to make selections on each subsequent page? Is that even possible?
I have included a quarter's worth of data if you need to examine what I am dealing with...
Hi Laura,
Great to hear you're making progress. In regards to slicers filtering other pages, please watch session 4.06 at about the 10 minute mark where I cover syncing slicers. You might also be interested in the drillthrough technique covered in 4.17.
Mynda
Thanks for the location of the lessons. That made total sense to me for the slicers to be applied through all pages. This has been an awesome set of lessons. I need to be more patient and just keep going without succumbing to pressure from my higher ups. I have until the end of September to get my example ready for show and tell and I think I now have enough to get me started for this proof of concept I am designing...
