@lukeSDM Ok, here we go.
First a couple quick questions:
1) What table is the Qualification slicer using?
2) What visualizations does the Qualification slicer need to filter?
Thinking of the answers to those questions, what you need to develop is a star schema model with Fact and Dimension tables.
Right now you have LOTS of Fact tables and from what I can see 1 dimension table - Pupil_Data. I can see that easily at a glance because Pupil_Data has lots of '1's while all the other tables have the '*'s meaning the Pupil_Data table contains each Adno 1 time, no duplicate values of Adno in that table. This is the perfect table for using in slicers and columns, categories, etc.
@lukeSDM UPDATE: I should note here that Fact tables generally do not link to each other as you can see with the arrows on the links. Even though there is a line from Predict Autumn to Pupil_Data to the duplicate Predict Autumn, the arrows both point to Pupil_Data, which means when you start at Predict Autumn 1, you can filter Pupil_Data, but then you get stuck - you can't swim upstream against that arrow to get to the duplicate Predicut Autumn table and therefore any slicers made using Predict Autumn 1 will not affect Predict Autumn 2.
Now that you want to filter by Qualification, we need a dimension table for Qualification that relates to any other table it needs to filter. This table should have each qualification exactly once, and can have any other info you might want about qualifications, but might simply look like:
| Qualification |
| Art |
| Maths |
| Food |
| Physics |
etc, listing all Qualifications.
Then, relate the newly created Qualifications dimension table to both Predict Autumn1 tables using Qualification columns (and also relate this table to any other of the Fact tables it needs to be able to filter if they exist).
Side notes for consideration (these won't solve anything, but are helpful to know):
The dotted lines are not being used in your visualizations unless you have written a DAX expression to use them.
The EAP, ethnicity, DOB seem to be present in many tables. Assuming this information stays constant for each Adno, you could save space and therefore speed by only loading it into the Pupil_Data table and keeping it out of the progress and attendance tables.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
https://sites.google.com/site/allisonkennedycv
