Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Hi @thmpsne ,
Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi - No this didn't solve the issue... I realised I really need to work with the data in a pivoted format! Thanks for trying though 🙂
Hi @thmpsne ,
1. You also could try the formula below.
Measure = CALCULATE(COUNT('Table'[Student ID]),FILTER('Table','Table'[Question Code] = "example_industry"))
2. > EDIT:
I used measure:
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |