Count Enrolled Days

[Anonymous]

Hello,

I am new to DAX and I’m trying to calculate attendance percentages.  We have two nursery schools, each with different teaching days within an academic year (i.e. a school can be closed on different days for various reasons).  Each student has unique enrollment start and end dates within each academic year.  I have a DAX formula figured out that calculates the enrolled days for one school, but I don't know the best way to make it work for either school.

The layout for the relevant tables is:

PersonKey is the unique identifier for each student (int).

ProgramSubTypeKey is the unique identifier for each school (int, either1458 or 1459).

TeachingDays_CA contains a 1 for full school days and .5 for half days (for school 1458).

TeachingDays_GP contains a 1 for full school days and .5 for half days (for school 1459).

The numerator (in this particular case) counts the present and tardy days:

CountStatusPT = CALCULATE(Count(Attendance[PersonKey]), Attendance[AttendStatus]="P" || Attendance[AttendStatus]="T")

(it works)

This is how far I got the denominator, but it only calculates the enrolled days for TeachingDays_CA (school 1458).  How do I adjust it so that it will calculate for whichever school that a student is/was enrolled in?  

EnrolledDays = 
CALCULATE(
  SUMX(
    FILTER(Dates2Gen, 
      Dates2Gen[CalendarDate] >= MIN(Enrollments[EnrollStartDate]) 
      && Dates2Gen[CalendarDate] <= MAX(Enrollments[EnrollEndDate]) 
      && Dates2Gen[CalendarDate] <= TODAY()
    ), 
    Dates2Gen[TeachingDays_CA]
  )
)

If it helps, here is a clip of a matrix showing the current results for the 2020-21 school year (which are incorrect because they are only counting CA teaching days regardless of which school the student was enrolled in).

(SchoolKey is the ProgramSubTypeKey)

Thanks,

J

[Anonymous]

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

[Anonymous]

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]

@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. 🙂

[Anonymous]

@Anonymous 

I'd like to point out that this model will become ambiguous as soon as you enable the inactive relationship in a measure and at the same time will not disable the one between Date2Gen and Attendance. I believe you know what it means for a model to be ambiguous but if not, please consult the documentation for Power BI or look for vids on YT by Alberto Ferrari on this issue.

Another problem with this model is... that it's simply incorrect 😞 If this is what you have to work with, then I'm really sorry but you'll have a hard time creating good, correct measures for it and the design will be very rigid, meaning the cost of maintenance will be very high.

[Anonymous]

@Anonymous 

Thank you for your feedback.   I had added the inactive relationship in order to count the enrollments, based on examples I'd seen online and in the Power BI documentation.

USERELATIONSHIP function (DAX) - DAX | Microsoft Docs

You wrote that the model is "simply incorrect".   The data is in Azure SQL, so the "tables" in the model are really just views that I can alter to be whatever is needed.  So I've been trying to alter the model to look more like the star schema examples that I've seen online.  

However, I still can't figure out how to create an EnrolledDays measure.  I've pasted the current version of the model below.  Is the model still incorrect?  If so, what do you recommend that I change?

J