Best practices for multiple fact tables and time frames

[elJukes]

Hi

I'm learning a lot from reading these forums, so thank you to everyone who posts here. I was wondering if I could tap into the collective wisdom and ask for some advice/guidance handling the following problem.

I work for a group of schools in the UK, we have a number of pupils on roll each of whom have a series of test results collected throughout the year.

To represent all of this we have four tables:

1. A group memberships fact table with these fields:
   • StudentId
   • YearGroup (Grade)
   • StartDate
   • EndDate
2. A test results fact table with these fields:
   • StudentId
   • TestYearGroup (Grade)
   • TestResult
3. A demographics fact table with these fields:
   • StudentId
   • DemographicLabel
4. A student dimension table with these fields:
   • StudentId
   • Gender
   • Demographic2
   • Demographic3
   • ...

All of the fact tables are currently linked to the dimension table on a student_id key which is unique to the student. The group memberships fact table and the demographics fact table are both linked to the student dimension table with a bi-directional filter relationship, whereas the test results table is a single direction link. There are no compound keys such as student_id & year group/grade in the model at the moment.

The third fact table might seem odd, but we need to be able to present information about all the different demographics in one table visual and this was the only way we could think of to get a table visual to play nicely.

Where we're really looking for guidance is on the group memberships and test results fact tables. We need to be able to display the following sorts of things:

1. For students currently in a particular year group/grade, what are their test results for that same year group/grade
2. For students currently in a particular year group/grade, what are their test results for their previous year group/grade
3. For students who were in a particular year group/grade last year, what were their test results for that same year group/grade
4. For students who were in a particular year group/grade last year, what were their test results for the previous year group/grade

We've come up a with a few ideas for how to do this, including measures that filter the group memberships table on a date, then using select value to get the year group/grade name from the group memberships table and using it to filter the test results table. We've also tried making a year group/grade offset column in both the group membership which we merged into the test results table so filtering on an offset of 0 would give us the most recent year group/grade a student is in along with the test results associated with that year group/grade.

What we'd really like to know is if either of these would be considered best practice and some alternative perhaps more efficient ways of doing it. We can work in both DAX and PowerQuery.

Any advice is gratefully received!

[v-juanli-msft · Accepted solution]

Hi @elJukes 

So far, i can work out your requirement 1 and 2

1.create a new table without relationship with other tables

yeargroup = VALUES('group'[YearGroup])

2. create measures in the group memberships fact table

condition1 = IF(SELECTEDVALUE(yeargroup[YearGroup])=MAX('group'[YearGroup]),1,0)

condition2 = IF(SELECTEDVALUE(yeargroup[YearGroup])=MAX('group'[YearGroup])+1,1,0)

3.

Add [YearGroup] from the new created table "yeargroup" into a slicer,

Add [condition1] in the visual level filter of the table called "test results for that same year group/grade",

Add [condition2] in the visual level filter of the table called "test results for their previous year group/grade"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elJukes]

Thanks @v-juanli-msft that's brilliant!