Data model for Survey data

[cwollett]

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.

[AllisonKennedy]

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

[cwollett]

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:

1. 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.
2. 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.

[AllisonKennedy]

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

[cwollett · Accepted solution]

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.

[Anonymous]

Hi @cwollett ,

Glad to hear you may have found a solution! If you're sure the issue has been resolved, could you mark this post as resolved? That way, others with similar issues can more easily find a solution and the community can see that the issue has been resolved.
Thanks, and if you need any further help, please feel free to contact

[AllisonKennedy]

@cwollett yes, RLS makes things a bit tricky - there's no way I'm aware to clear the RLS filters without creating a different field.