Combining data from two tables

[mscantle]

I have two tables:

Table one records the morning and afternoon attendance of each student in school. It contains the following columns:

- Adno (the admission number of a student which is unique to them)

- Statistical meaning (details of their presence or absence, eg Present, Unauthorised Absence, No Mark, etc)

- AM/PM (which session of the day)

- Mark date (from the register)

- PossLessons (the number of possible lessons they could attend in that session if they are present, so 4 for AM and 1 for PM)

- MonthName (calculated from the Mark date, eg Jan-24)

Each student has multiple rows in the table, one for each session (AM or PM) of every day:

I can use this to plot a graph of monthly possible lessons and use a slicer to show how each student's attendance varies over time.

Table two records the date and period of the day in which any student attends an extra coaching session. It contains the following columns:

- Adno (the admission number of a student which is unique to them)

- Mark (this is always a '4' because the raw data is obtained from another system and is pre-filtered to deliver only those lessons where a '4' was recorded in the register. This is important because it shows that a student attended an extra coaching session at that time)

- Date (from the register)

- Period Description (which lesson of the day attendance was recorded for eg BWed:4 means Period 4 of Wednesday in Week B)

- LinkMonthName (calculated from the Date, eg Jan-24)

So, each student has multiple rows in the table, with one row for each period of the day that they were located in the coaching area. I can use this to plot a graph of the number of periods each month they were in the coaching area.

This what the graphs look like for a single student:

I want to combine this data so that I can show in a graph, on a monthly basis for each student, the percentage of the total lessons attended that were spent in coaching. This would equate to dividing the total number of lessons in coaching each month by the total number of possible lessons attended each month (*100 of course!).

I cannot find a way of doing this. I cannot just use a single table containing all of the individual period marks for every student in the whole school as this would involve creating a huge data set (1500 students, 5 periods per day, for 200-ish days per year).

It is also a 'live' model, as attendance data is added daily, so any solution would need to be automated.

I know this is a big ask (and possibly a bit confusing), but it was the clearest way I could frame the problem.

Any help gratefully received.

PS I have created a Measure that gives the correct information in a Card, even when I use a slicer to adjust the time frame, but I can't get a plot of PercentLink against MonthName to work. Here's the measure:

PercentLink =

(DIVIDE(SUM('LessonAttendance'[ActualLink]), SUM('AttTest'[PossLessons])))*100

[audreygerred]

Do you have a Date dim table? Ideally you need a star schema model where you have fact and dimension tables. Your two fact tables with dates of transactions/occurrences/etc. would each have a join to your date table and the date for the chart would come from the dim table.

http://powerbiwithme.com/2023/07/30/the-star-schema-edition/

http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/ 

[mscantle]

Thanks Audrey. I'm afraid I'm a bit of a newbie when it comes to Power BI and databases in general, so I've put my pbix file on OneDrive:

Coaching.pbix

The strange thing is that the Card in the bottom right-hand corner is accurate if the whole period of 6th Sept 2023 to 22nd Jan 2024 is selected (for eg, if you select Adno 3732, the Card correctly reads 16.32% for the whole period of 6th Sept 2023 to 22nd Jan 2024). When I vary the date range using the date slider, the Card is no longer accurate (for Adno 3732, it should read 9.41% for Sept, 11.24% for Oct, 26.97% for Nov, 10.61% for Dec and 25.49% for Jan).

The top graph and the middle one are both correct too. However, the bottom graph (which is the one I'm trying to sort out) is a complete work of fiction!

I'm very confused!

[mscantle · Accepted solution]

I think I've fixed it by creating a separate table that has the MonthName and MonthCode in it. Then created relationships with the tables containing MonthName.