This is my first time posting so please forgive me if it's rough!
I'm relatively new to PowerBI, and my DAX is very basic. In my role, we are moving toward using PowerBI to present results of university student experience surveys (social science data). These surveys are administered through Qualtrics. The surveys are very customisable, and even the pathway that a respondent takes through the survey will differ conditionally based on their unique responses (e.g. they won't get prompted to answer questions about employment if they have already indicated that they aren't employed). Thus, my dataset is highly variable.
Despite the challenges, I'm able to do most of what I want, but have continually run into the issue that I'm presenting here.
I have unpivoted my data; prior to unpivoting, depending on respondents, there could be over 100 columns (question responses) per unique ID, for >3000 respondents. Please see a dummy and highly simplified sample below of what the data sort of looks like:
| Student ID | Question Code | Response Value |
| 123456789 | example_numberjobs | 2 |
| 123456789 | example_mainrole - Job Title | Teacher |
| 123456789 | example_industry | Education & Training |
| 123456789 | example_employtype | Working for an employer |
| 123456789 | example_employedoverall | Yes, employed at time of survey |
| 123789456 | example_numberjobs | 2 |
| 123789456 | example_mainrole - Job Title | Nurse |
| 123789456 | example_industry | Healthcare & Social Assistance |
| 123789456 | example_employtype | Working for an employer |
| 123789456 | example_employedoverall | Yes, employed at time of survey |
| 321654987 | example_employedoverall | No, not employed at survey time |
| 456123789 | example_employedoverall | No, not employed at survey time |
| 987654321 | example_numberjobs | 1 |
| 987654321 | example_mainrole - Job Title | Civil Engineer |
| 987654321 | example_industry | Construction |
| 987654321 | example_employtype | Self-Employed |
| 987654321 | example_employedoverall | Yes, employed at time of survey |
I think I need a measure that says something like: Based on Question Code "example_industry", return the count of Response Value in the corresponding row (for each unique student ID that has answered that question). Also, in this scenario I would be looking at Count, but occasionally I would need to return the Average.
How do I write a measure to parse out responses for "example_industry"; e.g. I want to create a table like:
| Construction | 1 |
| Education & Training | 1 |
| Healthcare & Social Assistance | 1 |
| Total | 3 |
I also know I can't just use visual level filters to solve this, as a whole dashboard of visualizations created using visual level filters doesn't 'work' - when you click and interact with one figure, each Visualization's filters contradict and no data is shown. I tried this already!
Currently I've gotten past the need to figure out how to do this by creating separate data tables but it's becoming increasingly apparent that is not a sustainable solution.
SOS! Please help!
Thanks in advance 🙂
EDIT:
I used measure:
Industry countx = COUNTX(FILTER('Employment Table', 'Employment Table'[Code]="gds_industry"), 'Employment Table'[Value]), which does work and I am able to create a bar chart of industry, but other visualisations created using measures from the same table and column 'go blank' when you click on one of the Industries, to filter the other page's visualizations... Anyway to avoid this?!
