many-to-many

2 Topics

Returning values from a many to many relationship
I have an event table and an employee table that share a many to many relationship. I want to to polulate the role that an employee had at the time of the event. For example, the first event occurred to "Bob" on 1/4/2020, and I can see in the Employee table that he was an Analyst at that date. But he was a consultant when the second event that involved "Bob" occurred. Event Table Date Name 1/04/2020 Bob 1/07/2020 Bob 3/05/2021 Jill 13/02/2022 Fred Employee Table Name Role Start Date End Date Bob Analyst 1/01/2020 1/06/2020 Bob Consultant 2/06/2020 1/01/2023 Jill Consultant 15/07/2018 3/12/2020 Jill Trainer 4/12/2020 1/01/2023 Fred Analyst 1/05/2021 1/01/2023 Desired Result Event Table Date Name Role 1/04/2020 Bob Analyst 1/07/2020 Bob Consultant 3/05/2021 Jill Trainer 13/02/2022 Fred Analyst I've been banging my head against the wall for a while on this but I figure it shouldn't be that hard!?! Any help would be appreciated.
Solved
markgsmith01
2 years ago Place DAX Commands and Tips
632Views
0likes
2Comments
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
kris_cs1
3 years ago Place DAX Commands and Tips
345Views
0likes
0Comments