Hi there
I’m trying to get my head around how to set up my data model - and I seem to only be able to make things work with many to many relationships and crossfiltering in both directions.
From all of Sam’s videos I understand this to be a bad thing - though I don’t fully understand why.
So each student has a dozen classes. Each class has twenty something students. Each student has sat multiple assessments. The assessments are graded in different ways.
What I am trying to achieve is a model where I can filter for any course and produce a set of graphs of student performance for that class that are filterable by student characteristics (Gender, Ethnicity etc)
I then want to be able to identify who the students are at any grade level within a class from the graphs (i.e. graphs filter table).
What I have seems to work - but is that just an illusion? (only works if I use implicit rather than explicit measures)
Any help or suggestions would be greatly appreciated.
Cheers
Mark
Yes I don’t recommend having your model setup this way. There will be a way to improve it.
Here’s some ideas. I can only really guess without seeing the model myself. If you are able to add a demo file that would help
Think about what should be your lookup tables or filtering tables.
Students
Classes
Assessments/Tests
Then what would be the fact table
Grades (maybe?)
I actually think your reasonably close you just might want to adjust the look of your model.
Try placing the students table in the top layer and the other ones as well if you have them,
Then the fact tables down in the bottom layer.
I detail all this in this course module below
Specifically here
See how you go with these ideas.
Sam
Thanks
I went through those videos again (I do hope to build in some of those ideas once I’ve got passed this stumbling block) and attached my randomised dataset
I don’t think I’ve missed an analogous example -Is this just it or is there a better way that I can get the courses and teachers to filter the students. I don’t seem to be able to make that a 1 way relationship
The fact that the explicit measures don’t filter the table from my stacked bar chart - Is that just how it is or is that a quirk of my measure/model
demo options.pbix (272.7 KB)
What’s wrong with just changing things around to this…
This is what I would do.
All I did was re-arrange the location of each table and now it’s in what I would deem a best practice setup.
Lookup tables up top filtering down to your fact tables down the bottom.
I think the key here maybe is just correctly identifying what are the lookup table and the fact tables to begin with maybe.
See any issues with this?
Sam
Thanks
I don’t think this work - My teacher and course slicers no longer filter the results
My problem with this is that I need the Course, Line and Teacher from the Options table to filter the results in the PATdemo and CEM tables. Therefore doesn’t Options need to be upstream of the students or at least upstream of CEM and PATdemo?
Cheers
Mark
Ok thanks I understand it better now.
You know this actually looks like the rare occurrence where the many to many might be valid here.
I had a play around with a few ideas and even though some other re-arrangements or different formula techniques can work this probably is the best option for this case.
For example you could use a function called TREATAS in your formula to create a virtual relationship between the options table and the PATdemo and CEM but I’ve decided that it probably over complicates everything a bit too much when the many to many is fine in the same situation.
See below for information about TREATAS.
If something can be done simpler I would always go for this option and that’s what I believe you should do in this case.
Chrs
Sam
Thanks - it does make me feel a bit better
Your waterfall analogy makes clear sense to me and obviously 95% of the time it is best practice.
I’d love to get my head around what are the consequences of having the two directions.
Do any of your videos go into what can go wrong? e.g. what are the possible unintended consequences
Cheers
Mark
I haven’t actually created a huge amount of content around many to many relationships, mainly because I hardly ever use them.
I’m working through quite a bit of new content and updating content inside Enterprise DNA Online, so might look to add something a bit more comprehensive about it shortly.
Sam
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
