two different granularities

[wilson_smyth]

Im trying to get data at two different granularities. Its easy at the lowest granularity, requires no extra work, but to create a measure to always get the count at a higher granularity is eluding me. Im clearly lacking understanding of context but would appreciate some help to fill the gap in my knowledge.

Scenario is:
There are courses, which have students enroled on them. Each course has 2 years, course instances.

Each student views a number of course matrials while attending the course.

The fact table shows which students viewed which documents.

I want to see the document view count at student level, and at course level, so that i can eventually calculate averages and ratios.

Ive been able to get the correct value in a calculated column on the Course table, using countx(RELATEDTABLE('fact'), 'fact'[docid])  , but id like to do this in a measure.

For Course C1, or any students on C1, the value should always be 11, for C2 the value should always be 8.

I have mocked up a powerbi example to help explain the scenario.

[Ashish_Mathur]

Hi,

This measure works

=if(ISBLANK([doc count]),BLANK(),CALCULATE([doc count],ALL(Student[studentname])))

Hope this helps.

[wilson_smyth]

hi ASHISH_MATHUR

This does indeed work, thanks! it does however demand that i list all dimensions in the calculation if i dont want it grouped by them. 

I assumed ALLEXCEPT would remove all filters from all dimensions, except what i specified, in this case ALLEXCEPT(course, course[course]), but it doesnt work as i expected.

Id like to understand why allxcept doesnt operate as i expect, i.e.  why

ALL(students), ALL(CourseInstance)  != ALLEXCEPT(course, course[course])

[Ashish_Mathur · Accepted solution]

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)))

[wilson_smyth]

apologies for the delay in responding.

I dont fully understand the statement "You cannot open the filter of CourseInstance. because the courses are linked to that.  You should only open all Students."

if i created a calculated column containing course, on courseInstance would that mean i can filter on course instance?

[Anonymous]

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?

[wilson_smyth]

thanks 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.

[Anonymous]

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.