I’m attempting to create a model of employee survey data for 3 different years (2016, 2018, 2020). I’ve run into an issue and need help.
Attached is the model. The survey data is in the Raw Data table and there are relationships to a Dates table and a Tenure Code table. An Employee History table with counts by year and tenure is included, but no relationships has been created between the other tables.
I’ve created a measure to get the employee counts by year. It calculates by year, but doesn’t break it out by tenure.
CALCULATE (SUM (‘Employee History’[Count]),
TREATAS (VALUES (‘Employee History’[Tenure]),‘Tenure Code’[Tenure]),
TREATAS (VALUES (Dates [Date]),‘Employee History’[Date]))
I can’t get a simple formula to count respondents by year using the established relationship between Raw Data and Dates. I’ve tried the following:
Total Respondents = COUNTROWS (‘Raw Data’)
Total Respondents = COUNT (‘Raw Data’[Respondent ID])
Total Respondents =
CALCULATE (COUNT (‘Raw Data’[Respondent ID]),
USERELATIONSHIP (‘Raw Data’[Start Date], Dates [Date]))
None of these are allowing me to show respondents by year when I select Year from the Date field. However, if I use a different date field [End Date] from the Raw Data table, I can create a visual that works as expected.
How can I modify the measure that will give me Employee counts by tenure group by year?
How can I modify the measure that will give me Respondents by tenure group by year?
These both need to work from a filter that uses the Dates table. The Respondents measure will be heavily used throughout the planned visuals.
Employee Survey - Enterprise DNA Help.pbix (395.4 KB)