Forum Discussion
Post Patrontwo different granularities
Hi,
You cannot open the filter of CourseInstance. because the courses are linked to that. You should only open all Students. This too would work
=if(ISBLANK([doc count]),BLANK(),CALCULATE([doc count],ALL(Student)))
My expectation is that with the right data structure you should be able to achieve this without too much trouble.
Your FACT table described is "Documents viewed by Students", which is giving you the explict link between those 2 datasets (documents and students).
To work out what courses this relates to, you need to consider how you are storing that information. How do you know what course a particular documented was viewed for? Do you link this data through the details related to the document (courses it relates to), or from the student (courses they are enrolled in)? I'm assuming in both cases a document and a student could be linked to multiple courses?
Could you add more data to your FACT table such that it might also provide a link to your course table, in the same way that it links to your document and student table?
Post Patronthanks for the reply.
Document access is counted purely by a record existing, if a fact row exists associating a student with a docid, then the student accessed that document. If no row exists, then the student did not access the document.
I think youre correct, with some re-architecture of the schema it could become easier, I would like to have kept all dimensional attributes in the dimensions though, and the hierarchy/snowflake of course -> courseInstance as this is the natural shape of the data.
Perhaps a better question would be why the measure to count the documents at a course level does not work.
Dax for measure on fact table to get count at higher granularity. i removed all filters except the course, but it still filters the count on students.
course level doc count measure(incorrect) = CALCULATE(countx('fact', 'fact'[docid]), ALLEXCEPT(courseInstance,course))
Full example pbix is linked to in the opening post in the thread if it helps.
For security reasons i'm unable to review your file, perhaps another community member will be able to do that.
Unlike in a normal databasing situation, sticking simply to FACT tables and dimensional tables avoids the main advantages of the Power BI data model. The reason we model data is to organise it in a fashion designed for purpose.
So what is the relationship conceptually from Document to Course? Does your documentent record have a course ID?
- wilson_smyth
Hmmm, its in my onedrive public folder. very odd. ive uploaded it to gdrive also, give it a go here
The course is linked to document through the fact table:
Course -> CourseInstance -> fact <- Document(for expemental purposes, ive added courseid to the fact but it does not exist there in reality at present.