cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elJukes Regular Visitor
Regular Visitor

Best practices for multiple fact tables and time frames

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Best practices for multiple fact tables and time frames

Hi @elJukes 

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

3.png

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"

4.png

 

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Best practices for multiple fact tables and time frames

Hi @elJukes 

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

3.png

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"

4.png

 

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 Regular Visitor
Regular Visitor

Re: Best practices for multiple fact tables and time frames

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 19 members 798 guests
Please welcome our newest community members: