Forum Discussion
Data model for Survey data
I work at a school that does class evaluations at the end of each term. I am trying to figure out how best to model and analyze this data and am having some issues with what I thought would be a removefilters measure. This will be a little involved, but hopefully you stick with me.
Data model - Dummy data
Class table with information about each class, something like:
| class_id | class_name | department |
| 1 | PSY 101 - General Psychology | psychology |
| 2 | PSY 111 - Statistical Reasoning | psychology |
| 3 | MATH 120 - College Algebra | mathematics |
Instructor table like:
| instructor_id | instructor_name |
| 1 | John Smith |
| 2 | Jane Doe |
| 3 | Bob Smith |
Question table, like (note that question type is important for classes with more than one instructor as course questions are asked once while the instructor questions are asked per instructor):
| question_id | question | question_type |
| 1 | Instructions were clear | Instructor |
| 2 | Activites were purposeful | Course |
| 3 | This course challenged me | Course |
Fact table sample row:
| class_id | instructor_id | student_id | question_id | response | response_text |
| 1 | 1 | 1 | 1 | 5 | Strongly Agree |
Needs/DAX Help
I want to make a visual that displays the average for an instructor compared to the average for their department.
For course-based questions, my instructor average measure is:
COURSE MEAN =
var _responses =
# --summarizing since you do not need instructor id for course-related questions
CALCULATETABLE(
SUMMARIZE(
'Fact Table'[COURSE_ID],
'Fact Table'[QUESTION_ID],
'Fact Table'[STUDENT_ID],
'Fact Table'[RESPONSE]
),
'Dim Questions'[QUESTION_TYPE] = "COURSE"
)
RETURN
AVERAGEX(_responses, [RESPONSE])
What DAX would I need to then make something that would go in a matrix to display the above value side-by-side with the department value? I tried using the following, but it gives me the same value for both measures:
DEPARTMENT MEAN =
var _responses =
# --summarizing since you do not need instructor id for course-related questions
CALCULATETABLE(
SUMMARIZE(
'Dim Courses'[DEPARTMENT],
'Fact Table'[QUESTION_ID],
'Fact Table'[STUDENT_ID],
'Fact Table'[RESPONSE]
),
'Dim Questions'[QUESTION_TYPE] = "COURSE"
)
RETURN
AVERAGEX(_responses, [RESPONSE])
If it matters, there will eventually be row-level security on the data model so that instructors only see their information. Does that mean I'm going to have to make another dimension table of data summarized per department so that it doesn't get filtered by course/instructor?
I would like to use this measure in more than one place, if possible. The most involved place would be a matrix that will likely include:
- Question
- Instructor Name
- Class Name
I tried to do this with a calculate including remove filters (question_id, class_id) but that did not work out.
Unfortunately the ALLSELECTED option will not work because we will have row level security on the dataset, so the RLS will act as an additional filter. The only way I have found to work around the RLS issue is to create an unrelated summarized table.
6 Replies
cwollett thanks for all the detail in your question.
What fields are you wanting / trying to use in your matrix? You need to consider that every field you put in the matrix adds a filter context to your measure calculation / result. So if you want a Departmental average, you need to clear all other filters coming from your matrix visual within the DAX measure itself.
Measure functions such as ALLSELECTED and ALLEXCEPT will be helpful for you here. Have you used those before?
Thanks for the response, AllisonKennedy ! Great point on the visual introducing filter context. I meant to include information about the matrix I'd be using in the original message but forgot to do so. That's added now.
To answer here - I would love to be able to use this measure in two ways:
- A KPI/Gauge that's something like the average for the current instructor with the goal as the department average. This would then only have the instructor as an added filter.
- A matrix that would have expandable rows for Instructor, Class, Question.
I think the same measure would work for both, yes? Since the removal of filters on instructor_id/class_id/question_id should fufill both scenarios?
I have used ALLSELECTED/ALLEXCEPT in the past, yes. I didn't try either of those yet. I'll give those a try tomorrow.
cwollett Yes, the same measure should work for both. How did you get on with using the ALLSELECTED? Have you solved this or do you still need further guidance?
