Survey Data - Unpivoting Causing Issues?

[thmpsne]

Hi everyone, 

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?!

[Anonymous]

Hi,

Check if this measure helps:

Survey Measure = 
VAR v_SurveyCnt = CALCULATE
( 
    DISTINCTCOUNT( SurveyData[Student ID] ), 
    ALLEXCEPT( SurveyData, SurveyData[Question Code], SurveyData[Response Value] ) ,
    FILTER( SurveyData, SurveyData[Question Code]="example_industry" )
 )
 RETURN
 v_SurveyCnt

Thanks.

[thmpsne]

Hi @Anonymous  - This 'works' but doesn't return the correct values when I use it in my actual dataset. 

It also doesn't solve the issue of filtering values that are in the same column as values for other visualisations. 

[jdbuchanan71 · Accepted solution]

@thmpsne 

Just testing againts your sample I created a couple measures.

Student Count = DISTINCTCOUNT ( 'Table'[Student ID] )

Industry = 
CALCULATE( [Student Count],
'Table'[Question Code] = "example_industry")

Job Title = 
CALCULATE( [Student Count],
'Table'[Question Code] = "example_mainrole - Job Title")

They work when you apply filters on say the student so it should work with filtering on other columns in your data as well.