Calculated Column in many-to-many relationship
I have a table (fact_student_result_averages) which stores the aggregated results for each student per assessment code per question attribute. I have another table (dim_resources) which stores student resouces by question attribute. Each resource then has a percentage threshold that the student must score below for the resource to be shown to them. There can be multiple resources per attribute and assessment code. eg: As far as I can tell, I can only use a many to many relationship here (without joining the tables to making a large flat file), which I know is rarely the correct option. However, I just don't understand how to make a condition to hide the resources that don't meet the threshold for each student. If I simply add the [Threshold] and [Result_Percentage_Average] columns to a table, the correct values are shown for each student, assessment code and attribute. However, as soon as I try to use those values in a calculated column that I can filter on, because there is a many-to-many relationship, it aggregates all thresholds with that [Attribute_Value_Code-Assessment_Code-Key], not only for student in that row. I can get it to work fine as a measure, but then I can't then filter on that. I have tried many combinations of ALLEXCEPT, SELECTEDVALUE etc with no luck. Surely I'm doing something really stupid? Thank you so much
