Relationship / Modelling Help

[kris_cs1]

Hi, I am hoping someone can help with this. 

I have the following relationships:

- A student has multiple results (although there are mutliple rows per student in uvLoginsAll_TB)

- A question has multiple results (one for each student)

- An exam has multiple questions 

I now need to include AARA information. However, each row in the AARA table has a relationship to a particular student for a particular exam only. I can seem to create one relationship but not both. 

Any help would be appreciated. I'm sure it is quite straightforward but I'm new to PBI from Tableau and want to get things right.

Thanks.

[kris_cs1]

Thanks so much. If there is a better way to structure the data, that would be great to know as well. We are just in the prototype phase.

[bolfri]

Hi Kris,

here is some sample prototype:

In Power Query M I've splitted "uvLoginsAll_TB" to generate dim_students table with qunique StudentID and fact_class table with information which students go to which classes. I coudn't find any connection to ClassCode to the rest of the tables, but I can imagine that each exam belongs to some specyfic class. Then it's better to create dim_class and connect it to fact_class and dim_exam.

I hope that make sense. 

With this model you can do anything you want in PBI. 😄 Notice that NONE of relationship is bidirectional and it suppose to be that way.

[kris_cs1]

That's amazing, thank you bolfri. I had to add in a few extra tables for class data like you mentioned. Here is what I have now. I'm positive this isn't completely correct. I am still getting my head around star schema. In Tableau, a lot of the time we used to create custom SQL views for each dashboard.

Nearly everything seems to be working in my visualisations as before. However, there are two things I am unsure of:

1) I want a subject slicer to filter a student slicer based on those who are in that subject. Similarily for classcode. Subject seems to filter classcode, but neither filter student name. What would be the best way to achieve this by modifying the relationships / structure.

2) Also closely related - previously , slicers only showed the subjects / students that had results. Now, they always display all subjects and all students.  How would I filter out subjects and student with no results from slicers?

[bolfri]

Too many bidirectional relationship. 🙂 Power BI is more flexible than Tableau IMO and Star Schema is best option to create a data model. Can you provide some sample data with ID only and some random name for 2-3 students and 1-2 exams etc, so I can show you?