Forum Discussion
Data model for Survey data
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.
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?
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.
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