I am building a dashboard to show progress of employees through a long-term training program. I am trying to create a measure to track progress against time-based expectations, and I'm struggling with the DAX logic.
The model I have is a variation on a star schema. The central table is a record of the course completions in our learning management system: columns include the participant ID, course ID, and completion status. The participant ID and course ID together make a unique key (and exist as a person-key column).
Participant ID is related to a participant table, which includes the participant ID and cohort ID. Participant table is the one side of the relationship.
The cohort ID is related to a cohort progress table, which includes the cohort ID and how many months the cohort has been in the program. Cohort progress is the one side side of the relationship.
Going back to the course completions table, the course ID is related to a course table, which includes the course ID and a column for what month in the program the course is due by. Course table is the one side of the relationship.
I want a set of measures to count completions based on time:
* How many person-course rows exist in the course completions table, in which the completions status is yes, and the participant's cohort month is greater than or equal to the course's month (i.e., the deadline for the course has passed for those people, and they have completed the course?
* How many completions are expected at this point in time (e.g., for each cohort, add up (the number of courses whose month is less than or equal to the cohort's month times the number of participants in that cohort)? This one is complicated because the course completions table is not guaranteed to have a row if the participant has not completed a specific course, for very weird reasons that are internal to the LMS.
What is the best way to set up these measures?
The model I have is a variation on a star schema. The central table is a record of the course completions in our learning management system: columns include the participant ID, course ID, and completion status. The participant ID and course ID together make a unique key (and exist as a person-key column).
Participant ID is related to a participant table, which includes the participant ID and cohort ID. Participant table is the one side of the relationship.
The cohort ID is related to a cohort progress table, which includes the cohort ID and how many months the cohort has been in the program. Cohort progress is the one side side of the relationship.
Going back to the course completions table, the course ID is related to a course table, which includes the course ID and a column for what month in the program the course is due by. Course table is the one side of the relationship.
I want a set of measures to count completions based on time:
* How many person-course rows exist in the course completions table, in which the completions status is yes, and the participant's cohort month is greater than or equal to the course's month (i.e., the deadline for the course has passed for those people, and they have completed the course?
* How many completions are expected at this point in time (e.g., for each cohort, add up (the number of courses whose month is less than or equal to the cohort's month times the number of participants in that cohort)? This one is complicated because the course completions table is not guaranteed to have a row if the participant has not completed a specific course, for very weird reasons that are internal to the LMS.
What is the best way to set up these measures?
