Hey folks, this is going to be a long thread, but 85% of it is just me providing context in the hopes that it helps identify a solution. Please bear with me, I'll be as brief as I can and sincerely need some help/guidance on this because I'm at a loss and totally stuck.
I have a report I've put together that is meant to show managers at my company whether or not their employees have completed a learning program. Programs are made up of multiple classes (with classes being grouped by theme within the program), and all classes in a program need to be completed for a learner to be marked complete/compliant. Here's an example of how the data is laid out:
| Program Title | Sub Group Title | Course Title |
| Power BI Certification | Microsoft Office | How to use MS Word |
| Power BI Certification | Microsoft Office | How to use Excel |
| Power BI Certification | Microsoft Office | How to use PowerPoint |
| Power BI Certification | DAX 101 | Using Custom Measures |
| Power BI Certification | DAX 101 | Best Practices for Slicers |
So in this instance, the learning program is called Power BI Certification, it has 2 subgroupings (Microsoft Office and DAX 101), and then 5 different courses (fyi, the same course can appear in multiple programs in case that matters).
So far so good! For my output, I put a table together that has an Employee's Name in Column A, the Program name in Column B, and in Column C is a header of Program Completed where the value returned is a yes or no. It looks like this:
| Employee Name | Program Name | Program Completed? |
| Larry Potter | Power BI Certification | Yes |
My goal was to simply give managers a high level view of every employee at our company (that would update if they applied slicers/filters) and a quick, accurate answer to the question "has employee x completed program y?".
The last part of that is where I'm experiencing issues. For whatever reason, the DAX measure I'm using is correct until filters are applied. The offending DAX measure is as follows:
Total Courses in a Program = IF ( HASONEVALUE ( CP[Column1] ) , COUNTROWS ( CP ) )
CP is the name of the sheet where the data is located and is structured the same as the program table I have above. I simply want it to count the # of courses in the relevant program and use that as a hard # (i.e. the # it returns is only a count of courses within the program, and as a result that # doesn't ever change based on filter selections). Here's where things get squirrely and where I can use help.
Using the example above (Power BI Certification), let's say I have an employee named Larry Potter. Here's his course completion history:
| Employee Name | Course Title | Completion Status |
| Larry Potter | How to use Excel | Complete |
| Larry Potter | Using Custom Measures | Complete |
Larry has only completed 2 of the required 5 courses needed to be marked "Complete" for the Power BI Certification. He's never even looked at the other courses (How to use Word, etc), and that's why there's no data for the remaining courses in the program, but he IS complete for 2 of them.
That's where my DAX measure starts misbehaving. The measure is this:
% Completed2 = IF (
DIVIDE ( [Count Course Completed], [Total Courses in a Program] ) >= 1, "Yes","No")
and just for visibility, here's what the [Count Course Completed] measure from the measure directly above is:
Count Course Completed = CALCULATE ( [Count Course], course_completions_report[Completion Status] = "Complete" )
[Count Course] is just a COUNTROWS formula for the entire data set.
If I don't filter anything and put the Total Courses In Program DAX measure from the beginning of this thread into a pie chart, it correctly shows that there are 5 courses in the Power BI Certification program. If it did that consistently, then the division would work out because 2 (how many courses in the program Larry shows "complete" for) divided by 5 (how many courses are in the program) is less than 1, and my DIVIDE formula is set to return that as a "No".
Once I filter things to look at him specifically (or anything detailed), the Total Courses In Program DAX changes the correct value of 5 and matches it to the total # of Larry's completions which is 2. As a result, 2/2 = 1 and that returns a "Yes" when that's actually wrong.
Hopefully that made some semblance of sense, but the gist of it is I'm trying to figure out how to make that Total Courses In Program DAX be static and correct all the time so that my other measures calculate properly.
I realize this was a ton to read and it may be offputting to even dive in to, but if there's anyone out there who thinks they have an inkling as to what I'm doing wrong, your input and/or potential solutions would be a huge help.
