Forum Discussion
Count Enrolled Days
Anonymous
This looks much better now but it's still not correct. But to tell you how a correct model should be built I'd need to have full information about the domain you're trying to mimic. I'll tell you, though, why it's incorrect. That's because the columns TeachingDays_CA and TeachingDays_GP are misplaced. They should be in a different table. And they should be consolidated into one column so that you can easily add more schools (more data) to the model without a single change to the design (and it really does not matter that you have 2 schools right now). Currently, you can't make this change without a major disruption (adding new columns) to the model. And this in turn means: it's incorrect.
As far as I can see, your model does not correctly mirror the business objects but I can't build the correct structure due to me not knowing the subject. Sorry.
By the way, Attendance and Enrollment are two different concepts and as such they require 2 different fact tables. There might be one more table missing. One that stores the mapping between Day and TeachingDays (with some other suitable dimensions).
Hi Anonymous ,
I tried creating a TeachingDays table after your first message, but was still unable to get the EnrolledDays calculation to work. However, your suggestion to make Enrollments into a fact table pointed me in the right direction. I created a Students dimension table and an Enrollments fact table with the EnrolledDays for each enrollment in it. A screenshot of the model is below.
I had thought I had this solved with that, however the calculations only work at the student level. The totals on the table visual are incorrect and the calculation does not work when I make a table visual at a higher level, such as school or class.
For example, only the green is correct:
Student level:
School level:The calculations are currently:
CountStatusPT = CALCULATE( COUNT( Attendance[AttendStatus]),
Attendance[AttendStatus]="P" || Attendance[AttendStatus]="T" )
AttPct = DIVIDE( [CountStatusPT], MAX( Enrollments[EnrolledDays] ) )
From the DAX reading I've been doing, it might need SUMX( ) or HASONEFILTER( ) or SUMMARIZE( ) and MAX( Enrollments[EnrolledDays] possibly needs to be made into a measure that can be used in the AttPct calculation... but I'm really not sure. My attempts have not been successful yet.
I'd really appreciate it if you (or someone) could point me in the right direction again. I feel like I'm so close! It works for a student, I just need to alter it so that it will roll up to higher levels.
Thanks,
J
Anonymous
OK. This starts to look like a real good star schema. But... there are still questions to be asked. How are enrollments associated with attendance? There should be some association between them, right? Associating them by dates and students is not enough in my view. There must be something else which is missing from the picture. Whatever it is, it cannot be that the two tables are connected directly since that would violate 2 rules of dimensional modeling in PBI. One of them being that 2 fact tables must never be connected directly. The other is that a model must never be or even become ambiguous after performing any operations on relationships (like USERELATIONSHIP or CROSSFILTER).
My guess is that one enrollment should be connected to many attendances. This means, in turn, that Enrollment should be a dimension. It also means that there should be a bridge fact table that associates enrollments with students and another one that associates enrollments with attendances. Then, there should be no association between Dates2Gen and the Enrollment dimension.
On top of that, you should always keep in mind one important rule. Fact tables' columns must never be exposed to the end user. Slicing and dicing must always be performed via dimensions ONLY.
Please remember tha such rules let you structure your model correctly.
By the way, I'd like you to also state the algorithm by which you're carrying out your calculations on ANY level of granularity. Then and only then will I be able to tell you what your DAX should look like. And, of course, the model must be correct. 🙂