I'm working with student data and I want to create a table visual containing a list of cohorts in the first column with attendance in the second. The cohorts are things like special needs Y/N, foreign language speaking Y/N, etc. and therefore can crossover. For simplicity, let's say all cohorts are Y/N.
My model is as follows:
Dataset A - unique row per student, contains cohort data (one column per cohort)
Dataset B - multiple rows per student, attendance per day per student
Dataset C - multiple rows per student, I created this as an attempt to achieve my goal, by copying the Dataset A then unpivoting all columns except the student ID
So say I have four cohorts, then C has four rows per student, with three other columns - one for cohort name (e.g. Special Needs), another for value Y/N, and another I made which is a concatenation of these two to show in the visual (e.g. Special Needs = Yes).
I have Dataset A > 1-* > Dataset B on student ID, and Dataset A > 1-* > Dataset C on student ID.
I have as columns in the table visual the concatenated column from C (e.g. Special Needs = Yes), and Attendance which is a measure calculating attendance using columns in B.
The problem is in the table I get the four cohorts in first column, eight rows (good) then one attendance figure for the entire student list repeated throughout the second column (bad). I want the second column to filter attendance for those students who are members of the cohort in the row e.g. row for Special Needs = Yes, attendance for students who have special needs. The attendance measure works everywhere else in my report, filtering on anything other than columns from C. I want to do this so users can sort across all cohort Y/N rows.
Is what I'm after possible?
